Reducing the Undo Tablespace Datafile size
448221Aug 1 2005 — edited Aug 2 2005After 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.