Oracle修改Tablespace的Name

Oracle修改TableSpace的Name

 

    在Oracle10g以前，tablespace的name是不可以随意修改的，只能drop掉之后重建，但是在10g之后，oracle加入了修改的功能，除了system和sysaux两个表空间外，其他的表空间都可以改名。今天专门来记录一下这个特性：

 

    为TableSpace改名的举例如下：

 

SQL> create tablespace wxq_tbs datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' size 1M;

 

Tablespace created.

SQL> alter tablespace wxq_tbs rename to wxq_tbs2;

 

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                                              STATUS
------------------------------------------------------------ -------------

SYSTEM                                                       ONLINE
UNDOTBS1                                                     ONLINE
SYSAUX                                                       ONLINE
TEMP                                                         ONLINE
USERS                                                        ONLINE
OWB_TBS                                                      ONLINE
RECOVERY_TBS                                                 ONLINE
STREAM_TBS                                                   ONLINE
WXQ_TBS2                                                     ONLINE

SQL> select tablespace_name,file_name,status from dba_data_files;

 

TABLESPACE_N FILE_NAME                                                         STATUS
------------ ------------------------------------------------------------------ -------------
USERS        /opt/oracle/product/10.2.0/oradata/wangxiaoqi/users01.dbf          AVAILABLE
SYSAUX       /opt/oracle/product/10.2.0/oradata/wangxiaoqi/sysaux01.dbf         AVAILABLE
UNDOTBS1     /opt/oracle/product/10.2.0/oradata/wangxiaoqi/undotbs01.dbf        AVAILABLE
SYSTEM       /opt/oracle/product/10.2.0/oradata/wangxiaoqi/system01.dbf         AVAILABLE
OWB_TBS      /opt/oracle/product/10.2.0/oradata/wangxiaoqi/owb_tbs01.dbf        AVAILABLE
RECOVERY_TBS /opt/oracle/product/10.2.0/oradata/wangxiaoqi/recover_tbs.dbf      AVAILABLE
STREAM_TBS   /opt/oracle/product/10.2.0/oradata/wangxiaoqi/stream_tbs01.dbf     AVAILABLE
WXQ_TBS2     /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf          AVAILABLE

 

 

    此时，datafile的名字没有改过来，与tablespace不一致，所以需要再改一下，这个过程相对来说比较复杂，要以下面的顺序来修改：

 

    1、把相应的tablespace改成read only;

    2、把需要修改的datafile置为offline;

    3、在操作系统中改名

    4、alter database rename file .. to ..;

    5、把相应的datafile置为online;

    6、把相应tablespace改成read write;

 

    具体操作如下：

 

SQL> alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf';
alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 11 - file is in use or recovery
ORA-01110: data file 11: '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf'

 

SQL> alter tablespace wxq_tbs2 read only;

 

Tablespace altered.

 

SQL> alter database datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' offline;

 

Database altered.

 

SQL> host mv /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf;

 

SQL> host ls -l /opt/oracle/product/10.2.0/oradata/wangxiaoqi/
total 6115528
-rw-r----- 1 oracle oinstall 1073750016 Jul 28 01:35 owb_tbs01.dbf
-rw-r----- 1 oracle oinstall   26222592 Jul 28 01:35 recover_tbs.dbf
-rw-r----- 1 oracle oinstall  209723392 Jul 28 01:35 stream_tbs01.dbf
-rw-r----- 1 oracle oinstall  471867392 Jul 28 16:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  566239232 Jul 28 16:08 system01.dbf
-rw-r----- 1 oracle oinstall   31465472 Jul 27 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall  513810432 Jul 28 16:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Jul 28 10:14 users01.dbf
-rw-r----- 1 oracle oinstall    1056768 Jul 28 16:08 wxq_tbs2.dbf

 

SQL> alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf';

 

Database altered.

 

SQL> alter database datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf' online;

 

Database altered.

 

SQL> alter tablespace wxq_tbs2 read write;

 

Tablespace altered.

 

SQL> select tablespace_name,file_name,status from dba_data_files;

 

TABLESPACE_N FILE_NAME                                                         STATUS
------------ ------------------------------------------------------------------ ------------------
USERS        /opt/oracle/product/10.2.0/oradata/wangxiaoqi/users01.dbf          AVAILABLE
SYSAUX       /opt/oracle/product/10.2.0/oradata/wangxiaoqi/sysaux01.dbf         AVAILABLE
UNDOTBS1     /opt/oracle/product/10.2.0/oradata/wangxiaoqi/undotbs01.dbf        AVAILABLE
SYSTEM       /opt/oracle/product/10.2.0/oradata/wangxiaoqi/system01.dbf         AVAILABLE
OWB_TBS      /opt/oracle/product/10.2.0/oradata/wangxiaoqi/owb_tbs01.dbf        AVAILABLE
RECOVERY_TBS /opt/oracle/product/10.2.0/oradata/wangxiaoqi/recover_tbs.dbf      AVAILABLE
STREAM_TBS   /opt/oracle/product/10.2.0/oradata/wangxiaoqi/stream_tbs01.dbf     AVAILABLE
WXQ_TBS2     /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf         AVAILABLE

 

8 rows selected.

 

    至此，就全部修改完成了。再强调一下，只有在10g中才能够修改，10g以前是不可以的。