Thursday, February 7, 2013

Drop Undo Tablespace Online

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string       AUTO
undo_retention                       integer     900
undo_tablespace                    string       UNDOTBS1


CREATE UNDO TABLESPACE undotbs2
DATAFILE'/u01/app/oracle/oradata/orcl/undotbs02.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M;
Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

System altered.

SQL> show parameter undo ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                 string          AUTO
undo_retention                       integer        900
undo_tablespace                    string          UNDOTBS2
 Now you need to check if there's any segment used old undo tablespace to ensure you will not loose any Data

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs where tablespace_name like '%UND%';

SEGMENT_NAME                   OWNER  TABLESPACE_NAME                STATUS
------------------------------ ------ ------------------------------ ----------------
_SYSSMU11$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU12$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU13$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU14$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU15$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU16$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU17$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU18$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU19$                     PUBLIC UNDOTBS2                       ONLINE
_SYSSMU20$                     PUBLIC UNDOTBS2                       ONLINE

Make Sure that you don't have any Segment Using Undo01/Old Undo Tablespace and if you have one wait until the transaction become Invalid Or expired.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
 Mission Done.


Thank you
Osama Mustafa

No comments:

Post a Comment