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
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;