oracle 表空间的相关操作

1、创建表空间

   create tablespace test datafile '/u01/app/oracle/oradata/TSH1/test01.dbf' size 100m autoextend on next 10m extent management local uniform size 10m;

2、创建临时表空间

create temporary tablespace testtemp tempfile '/u01/app/oracle/oradata/TSH1/testtemp01.dbf' size 100m autoextend on next 10m extent management local uniform size 10m;

3、建立undo表空间

create undo tablespace testundo datafile '/u01/app/oracle/oradata/TSH1/testundo01.dbf' size 100m autoextend on next 10m extent management local;

4、更改表空间状态

   4.1、表空间脱机

      Alter tablespace test offline;

  4.2、表空间联机

      Alter tablespace test online;

 

  4.3、数据文件脱机

      Alter database datafile 5 offline;

  4.4、数据文件联机

      Recover datafile 5;

      Alter database datafile 5 online;

  4.5、表空间只读

      Alter tablespace test read only;

  4.6、表空间可读写

      Alter tablespace test read write;

5、改变表空间下数据文件的大小及自动扩展

   Alter database datafile '/u01/app/oracle/oradata/TSH1/test01.dbf' resize 50m;

   Alter database datafile '/u01/app/oracle/oradata/TSH1/test01.dbf' autoextend on/off 

 

6、移动数据文件

  6.1、普通数据文件

   alter tablespace test offline;

   host mv /u01/app/oracle/oradata/TSH1/test01.dbf /u01/app/oracle/oradata/test01.dbf;

   alter tablespace test rename datafile '/u01/app/oracle/oradata/TSH1/test01.dbf' to'/u01/app/oracle/oradata/test01.dbf';

   alter tablespace online;

 

  6.2、系统数据文件

   Shutdown immediate;

   Startup mount;

   host mv '/u01/app/oracle/oradata/test01.dbf' '/u01/app/oracle/oradata/TSH1/test01.dbf';

   alter database rename file '/u01/app/oracle/oradata/test01.dbf' to'/u01/app/oracle/oradata/TSH1/test01.dbf';

   alter database open;

 

7、增加数据文件

  Alter tablespace test add datafile '/u01/app/oracle/oradata/TSH1/test02.dbf ' size100m;

8、删除表空间

  Drop tablespace test including contents and datafiles;