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

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=undo2 scope=both;

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;

TABLESPACE_NAME    STATUS        COUNT(*)
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;