Oracle Create Library

Oracle CREATE TABLE space SYS users log in as DBA under CMD: Play Sqlplus in cmd/Nolog and then Conn ./  asSYSDBA--to create a path if the path does not exist--Create a temporary table spaceCreate Temporarytablespace txwh_temp tempfile'E:\oracle\oradata\txwh\txwh_temp.dbf'size 50m Autoextend on  Next50m maxsize 20480m extent management local; --Create a data table spaceCreatetablespace txwh_datalogging datafile'E:\oracle\oradata\txwh\txwh_data.dbf'size 50m Autoextend on  Next50m maxsize 20480m extent management local; --Create a user and specify a tablespaceCreate UserTXWH identified byTXWHdefaulttablespace txwh_dataTemporarytablespace txwh_temp; --granting permissions to usersGrantConnect,resource toTXWH; //any database objects created later with that user will belong to the user_temp and user_data table spaces, without having to specify a tablespace for each object that is created:RevokePermissions... fromuser name; Delete user commandDrop User user_name Cascade; Create a table spaceCREATEtablespace data01datafile'/oracle/oradata/db/data01.dbf'size 500MUNIFORM size 128k; #指定区尺寸为128k, if not specified, the zone size by default is 64k delete table spaceDROPTablespace DATA01 including CONTENTS anddatafiles; Create a table spaceCREATEtablespace data01datafile'/oracle/oradata/db/data01.dbf'size 500MUNIFORM size 128k; #指定区尺寸为128k, if not specified, the zone size default is 64k two, the undo table space is establishedCREATEUNDO tablespace undotbs02datafile'/oracle/oradata/db/undotbs02.dbf'SIZE 50m# Note: At some point in the open state, only one undo tablespace can be used, and if you want to use a new tablespace, you must switch to that table space:ALTERSYSTEMSETUndo_tablespace=UNDOTBS02; Create a temporary table spaceCREATE Temporarytablespace temp_datatempfile'/oracle/oradata/db/temp_data.dbf'SIZE 50M Four, change the table space State1. Make Table space offlineALTERtablespace game OFFLINE; If you accidentally delete the data file, you must have the recover optionALTERTablespace game OFFLINE forRECOVER;2. To bring a table space onlineALTERtablespace game ONLINE;3. Take the data file offlineALTER DATABASEDataFile3OFFLINE;4. Bring the data file onlineALTER DATABASEDataFile3ONLINE;5. Make tablespace read-onlyALTERTablespace gameREAD  only;6. Making table spaces readable and writableALTERTablespace gameREADWRITE v. Delete table spaceDROPTablespace DATA01 including CONTENTS anddatafiles; Extension table space First look at the name of the tablespace and the file to which it belongsSelectTablespace_name,file_id,file_name,round(bytes/(1024x768*1024x768),0) Total_space fromDba_data_filesOrder  byTablespace_name;1. Adding data FilesALTERtablespace GameADDDataFile'/oracle/oradata/db/game02.dbf'SIZE 1000M;2. Manually increase the size of the data fileALTER DATABASEDataFile'/oracle/oradata/db/game.dbf'RESIZE 4000M;3. Set data File Auto-expansionALTER DATABASEDataFile'/oracle/oradata/db/game.dbfautoextend on NEXT 100MMAXSIZE 10000M; View tablespace information after setting select A.tablespace_name,a.bytes Total,b.bytes used, c.bytes free, (b.bytes*100)/a.bytes "% used", (c.bytes*100)/a.bytes '% free ' from SYS. Sm$ts_avail A,sys. Sm$ts_used B,sys. Sm$ts_free cwhere A.tablespace_name=b.tablespace_name and A.tablespace_name=c.tablespace

Simplified version:

--new tablespace to have the same name as the table space for the exported data--Create a temporary table spaceCreate Temporarytablespace ts_temp tempfile'D:\oracle\oradata\Oracle9i\user_temp.dbf'size 50mautoextend on   Next50m maxsize 20480m extent management local; --Create a data table spaceCreatetablespace ts_data Logging datafile'D:\oracle\oradata\Oracle9i\user_data.dbf'size 50m Autoextend on   Next50m maxsize 20480m extent management local; --Create a user and specify a tablespaceCreate UserUsername identified byPassworddefaulttablespace ts_dataTemporarytablespace ts_temp;--granting permissions to usersGrantConnect,resource toUsername

Oracle Create Library