In the server parameter file (SPFILE), the UNDO_TABLESPACE initialization parameter is set to UNDOTBS. You executed the following SQL statement to rename the UNDOTBS undo tablespace:
ALTER TABLEPACE undobts RENAME TO undobts_old;
Which statement is correct in this scenario?
A.
The tablespace will be renamed but the data file headers will not be updated.
B.
The above SQL statement will fail because you cannot rename an undo tablespace.
C.
The tablespace will be renamed and all the changes will be logged in the alert log file.
D.
The tablespace will be renamed but a message will be added to the alert log file indicating that you should change the corresponding initialization parameter files.
E.
To be able to rename the UNDOTBS undo tablespace, you would need to set the UNDO_TABLESPACE initialization parameter to some other tablespace name and then execute the above SQL command.
Explanation:
Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. The COMPATIBLE parameter must be set to 10.0 or higher. 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 log stating that the initialization parameter file must be manually changed.REF: Oracle(r) 10g Administrator Guide , 8-19