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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.