Tuesday, May 10, 2011

Switching Undo Tablespace

CREATE UNDO TABLESPACE "UNDOTBS"
DATAFILE '/u05/omr/oradata/undotbs.dbf' SIZE 4000M;

SQL> select name from v$tablespace where name like 'UNDO%';
NAME
--------
UNDOTBS1
UNDOTBS

SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS' SCOPE=BOTH;
SQL> show parameter undo

NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 21600
undo_tablespace string UNDOTBS

SQL>select status from V$ROLLSTAT
STATUS
ONLINE
ONLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

If the status is pending offline; u cannot drop undo tablespace UNDOTBS1

SQL>drop tablespace undotbs1 including contents and datafiles

If u drop u will get ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Note: You can find the following messages in alert.log after issuing alter system set command
Sat Jul 18 15:38:38 2009
Successfully onlined Undo Tablespace 7.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Sat Jul 18 15:46:28 2009
Undo Tablespace 1 successfully switched out.

No comments:

Post a Comment