oracle表空间操作模板.docx

oracle表空间操作oracle表空间操作 & CREATE TABLESPACE命令(2008-11-2513:24:19)转载标签:it分类:学****天地 select*fromdba_tableswhereOWNER='peter'CREATETABLESPACEdata01DATAFILE'D:\oracle\ora92\oradata\db\'SIZE200MUNIFORMSIZE128k;#指定区尺寸为128k,如不指定,区尺寸默认为64kCREATETEMPORARYTABLESPACEtemp_dataTEMPFILE'D:\oracle\ora92\oradata\db\'SIZE100MCREATEUSERpeterIDENTIFIEDBYpeterDEFAULTTABLESPACEdata01TEMPORARYTABLESPACEtemp_data;grantconnect,resource,dbatopeter; 一、建立表空间CREATETABLESPACEdata01DATAFILE'/oracle/oradata/db/'SIZE500MUNIFORMSIZE128k;            #指定区尺寸为128k,如不指定,区尺寸默认为64k二、建立UNDO表空间CREATEUNDOTABLESPACEUNDOTBS02DATAFILE'/oracle/oradata/db/'SIZE50M#注意:在OPEN状态下某些时刻只能用一个UNDO表空间,如果要用新建的表空间,必须切换到该表空间:ALTERSYSTEMSETundo_tablespace=UNDOTBS02;三、建立临时表空间CREATETEMPORARYTABLESPACEtemp_dataTEMPFILE'/oracle/oradata/db/'SIZE50M四、改变表空间状态ALTERTABLESPACEgameOFFLINE;如果是意外删除了数据文件,则必须带有RECOVER选项ALTERTABLESPACEgameOFFLINEFORRECOVER;ALTERTABLESPACEgameONLINE;ALTERDATABASEDATAFILE3OFFLINE;ALTERDATABASEDATAFILE3ONLINE;ALTERTABLESPACEgameREADONLY;ALTERTABLESPACEgameREADWRITE;五、删除表空间DROPTABLESPACEdata01INCLUDINGCONTENTSANDDATAFILES;六、扩展表空间首先查看表空间的名字和所属文件selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;ALTERTABLESPACEgameADDDATAFILE'/oracle/oradata/db/'SIZE1000M;ALTERDATABASEDATAFILE'/oracle/oradata/db/'RESIZE4000M;ALTERDATABASEDATAFILE'/oracle/oradata/db/AUTOEXTENDONNEXT