How to resize Undo

You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command:

ALTER DATABASE DATAFILE '+DATA/datafile/undotbs01.369.942861921' RESIZE 2048M;

ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well.

If you get the following error:
ORA-03297: file contains used data beyond requested RESIZE value

There are possibly some extents laying beyond the size you give to resize, so make sure there is nothing in the recycle bin

select * from recyclebin;
purge recyclebin;

If still not working, try to coalesce the tablespace

 SELECT S.tablespace_name,
         s.owner,
         s.segment_name,
         s.segment_type,
         SUM (s.bytes) size_in_bytes,
         ROUND (SUM (s.bytes) / 1024 / 1024, 2) size_in_m,
         f.file_name
    FROM sys.dba_segments s, sys.dba_data_files f
   WHERE     f.tablespace_name = s.tablespace_name
         AND f.file_id = s.header_file
         AND s.tablespace_name IN ('UNDOTBS01')
         AND f.file_name = '+DATA/datafile/undotbs01.369.942861921'
GROUP BY s.tablespace_name,
         s.owner,
         s.segment_name,
         s.segment_type,
         f.file_name
ORDER BY s.tablespace_name, s.owner, s.segment_name;

alter tablespace undo1 coalesce;

If the above fails, you will have to create a new UNDO tablespace and drop the existing on the primary database:
1. Create a new UNDO tablespace.

CREATE UNDO TABLESPACE undo02 SIZE 1048M;

For non ASM datafiles you have to specify the datafile:

CREATE UNDO TABLESPACE undo2 DATAFILE '/databases/undo02.dbf' SIZE 1024M;
or for ASM
CREATE UNDO TABLESPACE undo2 DATAFILE '+DATA' SIZE 1024M;

2. Modify the database parameter to use the new UNDO tablespace.

ALTER SYSTEM SET undo_tablespace=undo02 SCOPE=BOTH;
or for RAC
ALTER SYSTEM SET undo_tablespace=undo02 SCOPE=BOTH sid='*';

New transactions will begin using the new undo tablespace. After some time passes (at least the number of seconds specified by the UNDO_RETENTION initialization parameter), you can drop the old UNDO tablespace. This will mean that you will have 2 undo tablespaces for some time and that the new undo tablespace will have a new name.
On the primary database:

DROP TABLESPACE undo01 INCLUDING CONTENTS AND DATAFILES;

On the standby you may have to manually delete the datafile of the old undo tablespace.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.