[Oracle 10g] Tablespace renamed

Oracle [Oracle 10g] tablespace renamed Author: fenng
Date: 24-oct-2004
Source: Http://www.dbanotes.net
Version: 0.1

Simple Introduction
In previous versions of Oracle 10g, changing tablespace names was almost impossible, unless deleted, recreated, and a lot of work. Oracle 10g has added a new feature to change the table space name so that it is instantaneous to change the tablespace name. is a more humane function.

sql> COL file_name format a70sql> SET linesize 120sql> SET pagesize 99sql> COL tablespace_name format A10SQL&G T Sql> SELECT file_name, tablespace_name from Dba_data_files; file_name tablespace--------------------------------------------------------------------------------/u01/app/ ORACLE/PRODUCT/10.1.0/DB_1/ORADATA/TEST/USERS01.DBF users/u01/app/oracle/product/10.1.0/db_1/oradata/test/ SYSAUX01.DBF sysaux/u01/app/oracle/product/10.1.0/db_1/oradata/test/undotbs01.dbf UNDOTBS1/u01/app/oracle/ PRODUCT/10.1.0/DB_1/ORADATA/TEST/SYSTEM01.DBF system/u01/app/oracle/product/10.1.0/db_1/oradata/test/ EXAMPLE01.DBF example/u01/app/oracle/product/10.1.0/db_1/oradata/test/foo1.dbf FOO6 rows selected.

The syntax for this command is simple:

ALTER tablespace tablespacename RENAME to Newtablespacename;

Tablespacename and Newtablespacename correspond to the original table space name and the changed tablespace name respectively:
Practical Exercises
Note: Before and after the operation, please do the control file backup work

Sql>alter tablespace foo RENAME to test; Tablespace altered. Sql> SELECT file_name, tablespace_name from Dba_data_files; file_name tablespace--------------------------------------------------------------------------------/u01/app/ ORACLE/PRODUCT/10.1.0/DB_1/ORADATA/TEST/USERS01.DBF users/u01/app/oracle/product/10.1.0/db_1/oradata/test/ SYSAUX01.DBF sysaux/u01/app/oracle/product/10.1.0/db_1/oradata/test/undotbs01.dbf UNDOTBS1/u01/app/oracle/ PRODUCT/10.1.0/DB_1/ORADATA/TEST/SYSTEM01.DBF system/u01/app/oracle/product/10.1.0/db_1/oradata/test/ EXAMPLE01.DBF example/u01/app/oracle/product/10.1.0/db_1/oradata/test/foo1.dbf TEST

Because system and Sysaux are unique in the two table spaces, they cannot be renamed:

sql> ALTER tablespace system RENAME to Mysystem; Alter TABLESPACE system RENAME to Mysystem*error in line 1:ora-00712:cannot RENAME system tablespacesql> alter TABLESP ACE Sysaux RENAME to Mysysaux; ALTER tablespace Sysaux RENAME to Mysysaux*error on line 1:ora-13502:cannot RENAME Sysaux tablespace

You can rename the undo tablespace, and if you are using SPFile instead of Pfile, Oracle will automatically make changes to Undo_tablespace in SPFile (although it will not be observed until the database is restarted) if you are using a Pfile, you want to make a manual change to it. Let's look at the SPFile changes:

sql> ALTER tablespace undotbs1 RENAME to Undotbs; Tablespace altered. sql> sql> Show parameter pfile NAME TYPE VALUE----------------------------------------------------------------- ------------spfile string/u01/app/oracle/product/10.1.0/db_1/dbs/spfiletest.orasql> Show Parameters Undoname TYPE VALUE-----------------------------------------------------------------------------undo_management string Autoundo_retention integer 900undo_tablespace string undotbs1sql> shutdown immediate;database closed. Database dismounted. ORACLE instance shut down. Sql> Startuporacle instance started. Total System Global area 180355072 bytesfixed size 777996 bytesvariable size 128983284 bytesdatabase buffers 50331648 byte Sredo buffers 262144 bytesdatabase mounted. Database opened. Sql> Show Parameters undoname TYPE VALUE------------------------------------------------------------------------ -----undo_management string autoundo_retention integer 900undo_tablespace string undotbssql>

Renaming offline table space is not allowed:

sql> ALTER tablespace TEST OFFLINE; Tablespace altered. sql> ALTER tablespace test RENAME to Testoffline; ALTER tablespace test RENAME to Testoffline*error in line 1:ora-01135:file 6 accessed for dml/query is offlineORA-01110: Data file 6: '/U01/APP/ORACLE/PRODUCT/10.1.0/DB_1/ORADATA/TEST/FOO1.DBF '

Given the message is very reference value: Renaming operation is to the table space for dml/query operation, tablespace offline, then can not.

So what if the table space is read-only?

sql> ALTER tablespace TEST ONLINE; Tablespace altered. sql> ALTER tablespace TEST READ only; Tablespace altered. sql> ALTER tablespace test RENAME to Testreadonly; Tablespace altered. sql> list 1* SELECT file_name, tablespace_name from Dba_data_filessql>/file_name Tablespace--------------------------------------------------------------------------------/u01/app/oracle/ PRODUCT/10.1.0/DB_1/ORADATA/TEST/USERS01.DBF users/u01/app/oracle/product/10.1.0/db_1/oradata/test/sysaux01.dbf SYSAUX/U01/APP/ORACLE/PRODUCT/10.1.0/DB_1/ORADATA/TEST/UNDOTBS01.DBF undotbs/u01/app/oracle/product/10.1.0/db_1 /ORADATA/TEST/SYSTEM01.DBF system/u01/app/oracle/product/10.1.0/db_1/oradata/test/example01.dbf EXAMPLE/u01/app/ ORACLE/PRODUCT/10.1.0/DB_1/ORADATA/TEST/FOO1.DBF TESTREADONLY6 rows selected. Sql>

It appears that the data dictionary has been updated, but Oracle will write a log similar to the following in Alert_sid.log:

ALTER tablespace test RENAME to testreadonly Sat Nov 16:15:21 the ' test ' is tablespace to ' renamed '. Tablespace name change isn't propagated to file Headersbecause The tablespace are read only. Completed:alter tablespace test RENAME to Testreadonly

Notice that there is a tiny little bug:headersbecause in the log. This is two words, should be open:-)
Restrictive conditions
There is a major constraint on applying this feature: The compatible initialization parameter requires 10.0 or more higher to
Reference information

Oracle Database Administrator ' s Guide 10g Release 1 (10.1) Part number b10739-01 (note 62294.1)
The writer Fenng, a US-funded corporate DBA, who spends his spare time in various database-related technical forums and enjoys it. The current focus is on how to effectively build enterprise applications using Oracle databases. There is a little research on Oracle tuning and troubleshooting.
Personal technology site: http://www.dbanotes.net/. You can contact him via email dbanotes@gmail.com. Original source http://www.dbanotes.net/Oracle/10g_Rename_Tablespace.htm

Back Page <-|-> back page