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/pro

Oracle修改Tablespace的Name(一)