Switching Undo Tablespace

CREATE UNDO TABLESPACE "UNDOTBS"

DATAFILE '/u05/abc/oradata/undotbs.dbf' SIZE 4000M;

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

-------------------------

UNDOTBS1

UNDOTBS

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.