UNDO management, Cannot shrink the UNDO tablespace.

One common question we can  find in the internet is how to shrink the undo tablespace, because even if we see it empty we can get an ORA 3297 error (see here how to deal with ORA 3297 error), therefore, to resize it we need to drop it and recreate it.

Lets take a closer look at it:

1st.- Create a new UNDO tablespace (UNDO2).

SQL> CREATE UNDO TABLESPACE undo2 DATAFILE 'C:\Oracle\Oradata\testdb\undo2_01.dbf' SIZE 100M;

2nd.- We need to tell the instance to start using our new UNDO tablespace


3rd.- Remeber the UNDO_RETENTION parameter? this means that until that time expires and all transactions are finished we will not be able to drop the old UNDO tablespace (UNDO1)

Lets check for UNEXPIRED UNDO extents on our old UNDO tablespaces:

SELECT tablespace_name, status, COUNT (*)
FROM SYS.dba_undo_extents
GROUP BY tablespace_name, status
order by 1,2;

UNDO1              EXPIRED         23
UNDO1              UNEXPIRED        5

Status are:
ACTIVE: There are active transactions right now using the UNDO tablespace.
UNEXPIRED: These are extents that are not from an active transactions but are needed for the UNDO_RETENTION.
EXPIRED: Extents that are free to be reused.

While there are UNEXPIRED extents it won’t be possible to drop the UNDO1 tablespace. Once all extents are EXPIRED then we can dropt it.

4th.- Drop the old tablespace

SQL> DROP TABLESPACE undo1 including contents and datafiles;
This entry was posted in Oracle Server, SQL and tagged , , , , . Bookmark the permalink.

2 Responses to UNDO management, Cannot shrink the UNDO tablespace.

  1. Luis Pinto says:

    You forgot to mention that you need to make changes permanently in spfile:
    After following your instructions, if the database does not startup and complains with the error “ORA-30012: undo tablespace ‘UNDOTBS1′ does not exist or of wrong type” it’s necessary to commit the changes to spfile:

    1. alter system set undo_management=’MANUAL’ scope=spfile;
    2. shu immediate;
    3. startup
    4. create undo tablespace UNDOTBS datafile ‘location/filename’ size 1G autoextend on;
    5. alter system set undo_tablespace=’UNDOTBS’ scope=spfile;
    6. alter system set undo_management=’AUTO’ scope=spfile;
    7. shu immediate;
    8. startup
    9. show parameter undo

    • Hi, Thanks for the comment!
      I’m not sure I’m following why you doing all those commands and shutdowns, as my goal is to avoid recycling the database I’ll take your advice “partially” 🙂
      ALTER SYSTEM SET UNDO_TABLESPACE=undo2 scope=both;
      I’m updating the post with the update.

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