Oracle modifies the Name of Tablespace

Oracle modifies the Name of TableSpace
 
Before Oracle10g, the name of tablespace cannot be modified at will. It can only be dropped and re-built. However, after 10g, oracle added the modification function, except for the system and sysaux tablespaces, other tablespaces can be renamed. We will record this feature today:
 
An example of renaming TableSpace is as follows:
 
SQL> create tablespace wxq_tbs datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' size 1 M;
 
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
 
 
In this case, the datafile name is not changed, which is inconsistent with that of tablespace. Therefore, you need to modify it again. This process is relatively complicated and needs to be modified in the following order:
 
1. Change the corresponding tablespace to read only;
2. Set the datafile to offline;
3. Change the name in the operating system
4. alter database rename file... ..;
5. Set the corresponding datafile to online;
6. Change the corresponding tablespace to read write;
 
The procedure is as follows:
 
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 owb_tbs01.dbf
-Rw-r ----- 1 oracle oinstall 26222592 Jul 28 recover_tbs.dbf
-Rw-r ----- 1 oracle oinstall 209723392 Jul 28 stream_tbs01.dbf
-Rw-r ----- 1 oracle oinstall 471867392 Jul 28 sysaux01.dbf
-Rw-r ----- 1 oracle oinstall 566239232 Jul 28 system01.dbf
-Rw-r ----- 1 oracle oinstall 31465472 Jul 27 temp01.dbf
-Rw-r ----- 1 oracle oinstall 513810432 Jul 28 undotbs01.dbf
-Rw-r ----- 1 oracle oinstall 5251072 Jul 28 users01.dbf
-Rw-r ----- 1 oracle oinstall 1056768 Jul 28 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.
 
So far, all the modifications have been completed. It can be modified only in 10 Gb, but not before 10 Gb.



Author: sjmz30071360