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