Friday, March 14, 2008

Resize undo tablespace 10gR2 RAC

On RAC configuration each instance has assigned its own UNDO tablespace. So in order to resize the UNDO you must create a new one for each instance and assign it to the instance then you cane drop the old ones.

For each instance :

-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;

-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.

NOTE: Dropping the old tablespace may give ORA-30013 : undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.

No comments:

Post a Comment