Reduce UNDOTBS01
615484May 20 2008 — edited May 20 2008Hi Oracle Experts,
I have a production database. Version is 9i and it's running on RHES4 i686. The problem is that the undotbs01.dbf is now 32GB. I guess it was created with AUTOEXTEND and MAXSIZE being unlimited. I googled to find a way to reduce its size, but didn't want to try before asking you guys. There is a test database so I can try doing so before starting working on the production server.
I found that what I could do is, creating a new undo table space named undotbs02, assigning undo_tablespace do this new one, then droping the old one, creating a new undo table space undotbs01, assigning undo_tablespace to undotbs01 and finally droping undotbs02. Which sounds okay.
However, people say that I must be sure that there are no transactions. I don't know how to check that as it's a production server. Maybe I must shutdown then startup mount? But I'm not sure.
Could you please tell me what I should do to accomplish this and could you please guide me step by step?
To give you more info, I ran some select queries and here are the outputs:
UNDO_SIZE is 32GB
UNDO_BLOCK_PER_SEC is 9.111
DB_BLOCK_SIZE is 8192
ACTUAL_UNDO_SIZE [MByte] is 30985
UNDO_RETENTION is 10800
OPTIMAL_UNDO_RETENTION is 435275
Thanks in advance.