Skip to Main Content

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!

Reducing the Undo Tablespace Datafile size

448221Aug 1 2005 — edited Aug 2 2005
After a long running transaction, my undo tablespace datafile increased in size.
How do I now decrease its size?

I tried this. I first shutdown and restarted the database to make sure no
transaction outstanding and issued:

alter database datafile 'C:\oracle\oradata\mydb3\undotbs01.dbf' resize 500m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

but the above error displayed.

I dont understand why the undo tablespace is still containing so much data
after all transaction are committed. I suspected UNDO_RETENTION is the problem and changed
it to 1. But the same error still displayed.

Could anyone suggest a solution for me thx.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 30 2005
Added on Aug 1 2005
10 comments
1,060 views