Oracle: Advance Shrink UNDO Tablespace

Product: Oracle RDBMS
Version: 9.0.x - 19.3 (could be higher)
OS: Windows, Linux

Locally managed UNDO tablespace has been introduced about 20 yr ago in Oracle 9i. Oracle's documentation (KB) about shrinking it is never actually shrinking it, but replacing it with another tablespace with following steps:

1. Create 2nd new undo tablespace, e.g. UNDOTBS2
2. Set init parameter undo_tablespace=UNDOTBS2, the 2nd new undo tablespace above
3. Bounce DB
4. Drop old UNDOTBS1 tablespace, including its dbf files

This post going to go into more advance technique to drop existing automatically created rollback segments, and shrink the original UNDOTBS1 tablespace. This step will drop all rollback segments that automatically got created, followed by shrinking the files. As UNDOTBS1 tablespace is clean, we can shrink the dbf file. This procedure will be using hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS" as without it, we can't drop offline rollback segments that auto created.

Preparation:

1. Ensure DB can be safely brought down for maintenance, as this will require to bounce the DB few times
2. Make a backup of spfile[SID].ora in $ORACLE_HOME/dbs/ directory. The maintenance will modify it, and backup is the fastest way to revert back to original setting

Steps:

1. List down all rollback segments in tablespace UNDOTBS1:

SELECT segment_name FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';

2. Creates a statement to set hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS" like below with maximum of 255 characters long:

alter system set "_OFFLINE_ROLLBACK_SEGMENTS" = '_SYSSMU1_2270612333$,_SYSSMU2_1117599206$,_SYSSMU3_2866369812$' scope=spfile;

3. Shutdown immediate

4. Startup

5. Generates DROP ROLLBACK SEGMENT statement dynamically:

SELECT 'drop rollback segment "'||segment_name|| '";' FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';

6. Run above generate DROP ROLLBACK SEGMENT to drop all segments that listed in Step 2 above

7. There will be only some UNDO segments that we configured in Step 2 above that we can drop. In order to drop more, repeat step #1 (to get updated list) to 6

8. Double check only 1 rollback segments in UNTOTBS tablespace left, which is auto created

SELECT segment_name FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';

9. If there is more, then repeat Step 1 - 6

10. Shrink UNTOTBS1 dbf file, in this example, it is file ID 3, and shrinks it to 100MB

select tablespace_name, file_id, file_name, bytes/1024/1024 mb, maxbytes/1024/1024 mb_max from dba_data_files where tablespace_name = 'UNDOTBS1';

alter database datafile 3 resize 100m;

11. Optionally set its maxsize to 1000 MB

alter database datafile 3 autoextend on maxsize 1000m;

12. Shutdown, and restore original spfile.ora. This will indirectly remove the hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS"

shutdown immediate

cp -p $ORACLE_HOME/dbs/spfile[SID].ora.org 

13. Startup

14. Now DB will auto creates 1-2 new undo rollback segments in UNDOTBS1 tablespace

SELECT segment_id, segment_name, tablespace_name, status, next_extent, max_extents

FROM dba_rollback_segs;