• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Drop Undo Tablespace in Oracle Database

by admin

This post provides the steps to drop an Undo tablespace.

The Undo tablespace can be dropped if it is not the default Undo tablespace of the database and all the Undo segments in it are OFFLINE.

1. check whether the Undo tablespace to be dropped is the default Undo tablespace:

sql> show parameter undo

The tablespace name mentioned by the parameter UNDO_TABLESPACE is the current default Undo tablespace.

2. If the UNDO_TABLESPACE is set to the one to be dropped, create a new Undo tablespace and switch to the new one. Else, skip to step 3. Else, determine the size of the datafile(s) for your current undo tablespace “UNDOTBS1”:

sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name=[tablespace_name from the above output];

For example:

sql> select tablespace_name, file_id, file_name,round (bytes / (1024 * 1024), 0) total_space from dba_data_files where tablespace_name='UNDOTBS1;

Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

sql> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\UNDOTBS02.DBF' size 500M;

Switch to the new Undo tablespace.

sql> alter system set undo_tablespace=UNDOTBS2 scope=both;

3. Check the status of the Undo segments in the Undo tablespace to be dropped.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=[undo tablespace to be dropped];

For example:

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1';

The status ‘PARTLY AVAILABLE‘ means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. In case of a dead transaction, you will have to wait until the recovery is done.

If the Undo Segment status is ‘NEEDS RECOVERY‘, please refer the post below to recover the Undo segment.

Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery

4. If all the Undo segments in the tablespace to the dropped is of status OFFLINE, then drop the tablespace.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

5. Verify and then drop:

sql>Drop tablespace [tablespace_name] including contents and datafiles;

For example:

sql> Drop tablespace UNDOTBS1 including contents and datafiles;

Filed Under: oracle

Some more articles you might also be interested in …

  1. ORA-06512: at line num
  2. How does the RMAN Retention Policy Obsolete Incremental Backupsets
  3. Oracle 11G RMAN – Understanding UNDO backup optimisation
  4. How to Identify Different File types and space used in Flash Recovery Area
  5. How to Backup and Restore Java Classes and Privileges only in Oracle Database
  6. How to Optimize a Data Guard Configuration
  7. Difference between SQL and SQL*Plus Statements
  8. ASM background processes in 11gR2
  9. Oracle Database : script to create a “CREATE SYNONYM Script”
  10. How to Change SYS and SYSTEM Passwords in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright