Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

I get [ORA-03297] in RISIZE for UNDO

kazuaki arakiMar 13 2025 — edited Mar 13 2025

I want to RESIZE the UNDO area.
However
ALTER DATABASE DATAFILE '***.dbf' RESIZE 1024M;.
will cause [ORA-03297].

There is enough unused space for UNDO.
SHOW PARAMETER UNDO_RETENTION;.
IS 900
The same error occurs after this time.
Restarting ORACLE does not change it.
It is possible after a few days.

*Investigating, I also found information about creating another [UNDO UNDO_TABLESPACE].
However, I have not tried this one yet.
I am not sure if it is possible to DROP when it is not possible to resize, and I am not sure if it is the correct method.
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'path_to_undo_datafile2.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

https://forums.oracle.com/ords/apexds/post/cannot-shrink-the-undo-tablespace-4662

The version is Oracle11g Release 11.2.0.4.0.
I would like to know the cause and solution.
Thank you in advance.
This post has been answered by Solomon Yakobson on Mar 13 2025
Jump to Answer

Comments

Solomon Yakobson
Answer

UNDO retention simply affects when UNDO extent expires. Extent itself doesn't go anywhere. That's why ORA-03297. So with UNDO shrinking we don't have much choice besides creating new UNDO tablespace, switching system to new UNDO and (very important step):

select  tablespace_name,
       status,
       count(*)
 from  dba_rollback_segs
 where tablespace_name = 'your-old-UNDO-tablespace-name'
 group by tablespace_name,
          status
/

to make sure all old UNDO tablespace rollback segments are in OFFLINE status (you might need to wait up to tuned_undoretention time from v$undostat). Only then you can drop old UNDO tablespace.

SY.

Marked as Answer by kazuaki araki · Mar 14 2025
1 person found this helpful
kazuaki araki

Thank you. solomon

new UNDO tablespace create, switching system to new UNDO
I'll give this a try.

>UNDO retention simply affects when UNDO extent expires. Extent itself doesn't go anywhere.

I didn't get a deep understanding of this.
If you have a more detailed explanation or a link where I can get that information, please let me know.

Solomon Yakobson

@kazuaki-araki >UNDO retention simply affects when UNDO extent expires. Extent itself doesn't go anywhere.I didn't get a deep understanding of this.

UNDO extent expired simply means any transaction can now use that extent and write over what was previously stored in that UNDO extent. Extent itself is still there and occupies space. Expired UNDO extent ≠ free extent like it happens with, say, table used extents when table is dropped with purge and Oracle marks such extents as free. There is nothing special about UNDO resizing. It follows same rules as any other datafile type resizing - Oracle simply checks if there are used (not free) extents above requested datafile size and raises ORA-03297 if it finds one. It would be nice though if Oracle would check tablespace type and treat expired extents for UNDO tablespaces as free when resizing datafile.

SY.

Jonathan Lewis

@kazuaki-araki Here's a link to a note I wrote years ago about sorting out a problem with an undo tablespace that was “full”. It won't help you address your problem but it may help you understand how undo segments can grow and shrink.

Regards

Jonathan Lewis

kazuaki araki

Thank you . solomon

>It would be nice though if Oracle would check tablespace type
and treat expired extents for UNDO tablespaces as free when resizing
datafile.

You are right.
I will now try to shrink UNDO.
Thanks for everything. You've been a big help.

kazuaki araki

Thank you Jonathan.

Your blog had a lot of what I wanted to know.
I will refer to it.

Jonathan Lewis

It would be nice though if Oracle would check tablespace type and treat expired extents for UNDO tablespaces as free when resizing datafile.

Or even go so far as treating offline undo segments as free for the purposes of resizing. Technically it is possible to shrink or drop undo segments - but only by setting an undocumented parameter first. But there is a MOS note about that - 1580182.1 according to my library note.

Regards

Jonathan Lewis

kazuaki araki

Thank you Jonathan.

The information you gave me was important.
I was also able to get to some other information when I was researching from your information.
Apparently I may not have done enough research.
thanks for your support.

1 - 8

Post Details

Added on Mar 13 2025
#ora-03297
8 comments
92 views
1 mention