• 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. What are the implications of rebuilding indexes in Oracle Database
  2. Handling Exceptions in PL/SQL
  3. Oracle Home Relinking Interview Questions and Answers
  4. How to find daily and hourly archive log generation in Oracle Database
  5. How to resize an OCFS2 filesystem on Linux
  6. Smart scan for ASM disk group in Exadata
  7. How to Move OCR, Vote Disk File, ASM SPILE to new Diskgroup
  8. WebLogic Server Domain: How To Disable the HTTP methods other than GET and POST (such as PUT, DELETE, etc.)
  9. Script To Get Tablespace Utilization In Oracle Database 12c
  10. How to Drop Existing Temporary Tablespace and create new in Oracle 11g

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright