[Oracle 10g] table space was renamed Author: Fenng
Date: 24-OCT-2004
Source:
http://www.dbanotes.net
Version: 0.1
basic introduction
In Oracle 10g Previous versions, change the tablespace name is almost impossible, unless delete, recreate, costly. Oracle 10G adds a function of changing the name of the table space so that the names of the table space can be changed. It is a more humane function.
SQL> Col File_Name Format A70
SQL> SET LINESIZE 120
SQL> SET PAGESIZE 99
SQL> Col TableSpace_name Format A10
SQL>
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 foo
6 rows selected.
The syntax of this command is simple:
Alter TableSpace TableSpaceName Rename To NewTablesPacename;
TableSpaceName and NewTablesPacename respectively correspond to the original table space name and after the changed tablespace name:
Actual exercise
Note: Please do a good job of control files before and after operation.
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 of the particularity of the two tablespaces of System and sysaux, it is not a renamed name:
SQL> ALTER TABLESPACE SYSTEM RENAME TO MYSYSTEM;
Alter TableSpace System Rename to MySystem
*
Error At Line 1:
ORA-00712: Cannot Rename System TableSpace
SQL> ALTER TABLESPACE SYSAUX RENAME TO MYSYSAUX;
Alter TableSpace Sysaux Rename to Mysysaux
*
Error At Line 1:
ORA-13502: Cannot Rename Sysaux TableSpace
You can rename the undo tablespace, if you are using spfile, instead of pfile, Oracle automatically changes the undo_tablespique in the spfile (but you can observe after the database is restarted). If you are using Pfile, you want to Made in manual changes. Let's take a look at the change of spfile:
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.ora
SQL> Show Parameters Undo
Name Type Value
----------------------------------- --- ---------------------------
undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs1
SQL> Shutdown Immediate;
Database closed.
Database dismount.
Oracle Instance Shut Down.
SQL> Startup
Oracle Instance Started.
Total System Global Area 180355072 Bytes
Fixed size 777996 bytesvariable size 128983284 bytes
Database buffers 50331648 BYTES
Redo buffers 262144 bytes
Database mounted.
Database opened.
SQL> Show Parameters Undo
Name Type Value
----------------------------------- --- ---------------------------
undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs
SQL>
The name of the offline watch is not allowed:
SQL> ALTER TABLESPACE TEST OFFLINE;
TableSpace altered.
SQL> ALTER TABLESPACE TEST RENAME TO TESTOFFLINE
Alter TableSpace Test Rename to Testoffline
*
Error At Line 1:
ORA-01135: File 6 Accessed for DML / Query IS Offline
ORA-01110: Data File 6:
'/u01/app/oracle/product/10.1.0/db_1/oradata/test/foo1.dbf'
The given prompt information is very reference value: The rename operation is to perform DML / query operations for the table space, and the table space is OFFLINE.
So what happens if the table space is read?
SQL> ALTER TABLESPACE TEST Online;
TableSpace altered.
SQL> ALTER TABLESPACE TEST READ Ion
TableSpace altered.
SQL> ALTER TABLESPACE TEST RENAME TO TESTREADONLY
TableSpace altered.
SQL> List
1 * SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES
SQL> /
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 testreadOnly
6 rows selected.
SQL>
It seems that the data dictionary has been updated, but Oracle will write to alert_sid.log: Logs like the following:
Alter TableSpace Test Rename to TestreadOnly
Sat Nov 13 16:15:21 2004
TABLESPACE 'TEST' is renamed to 'TestReadonly'.
TableSpace name change is not propagated to file headersbecause the tablespace is ready.
Completed: Alter Tablespace Test Rename to TestreadOnly
Note that there is a subtle small bug: HeadersBecause. This is two words, should be empty :-)
limitation factor
Apply this feature has a major restriction condition: Compatible initialization parameter requirements are 10.0 or higher
Reference Information
Oracle Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 (Note 62294.1)
Letter author
Fenng, a US-raising company DBA, spare time, mixed with each database, is not tired. Currently payment of how to use the Oracle database effectively build enterprise applications. A little study on Oracle Tuning, Troubleshooting.
Personal technology site:
http://www.dbanotes.net/. Can via email
DBANOTES@gmail.com Contact him.
Origin
http://www.dbanotes.net/oracle/10g_rename_tablespace.htm
Back to page <- | ->
Back home
