How To Rename a Tablespace and Avoid Certain Errors temp tablespace 也可以rename的

Goal

This article will provide information on:

1. The Rename Tablespace feature
2. Common errors encountered when performing a Rename Tablespace
3. How to avoid those errors when renaming a tablespace

Solution

The Rename Tablespace feature:
-----------------------------------------------

Using the RENAME TO clause of the ALTER TABLESPACE, we can rename a permanent or temporary tablespace. When we rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.


Examples of errors encountered when renaming a tablespace:
-----------------------------------------------------------------------------------------

The following will affect the operation of this statement:

- The COMPATIBLE parameter must be set to 10.0 or higher.
- If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error will be raised.

SQL> alter tablespace SYSAUX rename to SYSAUX1;
alter tablespace SYSAUX rename to SYSAUX1
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

SQL> alter tablespace system rename to system1;
alter tablespace system rename to system1
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

- If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.

SQL> alter tablespace users offline;
Tablespace altered.

SQL> alter tablespace users rename to users1;
alter tablespace users rename to users1
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: '+SOMDG1/som10g/datafile/users.259.1'

- Alter the tablespace online and then rename it.

SQL> alter tablespace users online;
Tablespace altered.

SQL> alter tablespace users rename to users1;
Tablespace altered.

- If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.

SQL> alter tablespace users1 read only;
Tablespace altered.

SQL> alter tablespace users1 rename to users;
Tablespace altered.

SQL> alter tablespace users read write;
Tablespace altered.

- If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES view shows the new name.

SQL> alter database som10g default temporary tablespace lmtemp3;
Database altered.

SQL> select * from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE LMTEMP3
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE USERS
Name of default permanent tablespace

SQL> alter tablespace lmtemp3 rename to lmtemp2;
Tablespace altered.

SQL> select * from database_properties where property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE LMTEMP2
Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE USERS
Name of default permanent tablespace

- Renaming a default permanent tablespace.

SQL> alter tablespace users rename to users1;
Tablespace altered.

SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS1
TEMP
UNDOTEST
LMTEMP1
LMTEMP2

- You cannot rename a tablespace group:

SQL> alter tablespace tempgrp1 rename to tempgrp;
alter tablespace tempgrp1 rename to tempgrp
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [tbsrnm_03], [7], [], [], [], [],
[], []

- If the tablespace is an undo tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE).

The server parameter file was used to start up the database.

The tablespace name is specified as the UNDO_TABLESPACE for any instance.

If a traditional initialization parameter file (PFILE) is being used then a message is written to the alert file stating that the initialization parameter file must be manually changed.

SQL> alter tablespace undotest rename to undotest1;
Tablespace altered.

SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
UNDOTEST1
LMTEMP

- Renaming a tablespace in a Tablespace Group:

SQL> alter tablespace lmtemp1 rename to lmtemp;
Tablespace altered.

SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS1
TEMP
UNDOTEST
LMTEMP
LMTEMP2

How to Avoid Errors when renaming a tablespace:
------------------------------------------------------------------------

1. Tablespace is not a SYSTEM or SYSAUX tablespace.
2. Tablespace and all of its datafiles are ONLINE.
3. Check the alert.log for additional messages.
4. Compatible parameter must be set to 10.0 or higher.
5. Tablespace is not a Tablespace Group.

-----temp tablespace

Goal

Is it possible to rename a tempfile in Oracle 10g and later versions.

 

Solution

In Oracle 10g there is a New syntax in the database_file_clauses lets to rename tempfiles as well as datafiles. So it is possible to rename a tempfile.

Reference:
---------
<<http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/wnsql.htm#sthref6>>
Oracle Database 10g Release 2 New Features in the SQL Reference.

STEP TO BE FOLLOWED:
===================
If the file is the default temporary tablespace then you need perform the below from step 1,

Else if you want to move a datafile of a normal temporary tablespace then perform the below from step 2.

1]. First of all you have to create another default temporary tablespace. (pr_temp in our example)
2]. Take the tablespace in offline position
3]. Use os command to move or copy the files.
4]. Then perform:

SQL> alter database rename file '<PATH>\<NAME>' to '<NEW_PATH>\<NEW_NAME>';

5]. Bring the tablespace online. 

6]. If necessary or desired, change the default temporary tablespace back to the original.

EXAMPLE:

STEP 1:
------
SQL> alter database default temporary tablespace pr_temp;
Database altered.

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties
where PROPERTY_NAME like 'ÞFAULT%';

PROPERTY_NAME PROPERTY_VALUE
-----------------------------------------------
DEFAULT_TEMP_TABLESPACE PR_TEMP

STEP 2:
-----
SQL> alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF' offline;
Database altered.

STEP 3:
------
SQL> select file_name from dba_temp_files where tablespace_name='TEMP';

FILE_NAME
-------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.DBF

+++ Use os command to move or copy the files.

STEP 4:
------
SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TEMP01.dbf'
to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.dbf';
Database altered.

Now we can check if the file is move and renamed as below,

SQL> select file_name from dba_temp_files where tablespace_name='TEMP';

FILE_NAME
------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF

STEP 5:
------
SQL> alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEMP1.DBF' online;
Database altered.