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以前是不可以的。
?
?