Oracle modifies the Name of Tablespace

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.