Renaming, relocate resize Oracle undo tablespace.
On Oracle Applications 11i and Oracle database 9.2.0.7:
How to relocate Oracle undo tablespace.
Today: 09-Jan-2009
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
Do this off hours when users are out.
On database TEST:
From /u11 to /u12:
1) Create a temporary indo tablespace:
create undo tablespace APPS_UNDOTS2
datafile ‘/u12/oradata/testdata/rbstm01.dbf’ size 2000M;
alter system set undo_tablespace=APPS_UNDOTS2;
Verify the newly created undo tablespace:
select tablespace_name, segment_name, status
from dba_rollback_segs;
2) Drop the old undo tablespace and recreate it into the new desired location:
drop tablespace APPS_UNDOTS1;
–>remove the corresponding datafiles;
create undo tablespace APPS_UNDOTS1
datafile ‘/u12/oradata/testdata/rbs01.dbf’ size 2000M;
alter system set undo_tablespace=APPS_UNDOTS1;
Verify the newly created undo tablespace:
select tablespace_name, segment_name, status
from dba_rollback_segs;
3) Drop the temporary undo you created on setp 1”
drop tablespace APPS_UNDOTS2;
4) Resize the newly created undo tablespace:
ALTER TABLESPACE APPS_UNDOTS1
ADD
DATAFILE ‘/u12/oradata/testdata/rbs02.dbf’ SIZE 2000M;
ALTER TABLESPACE APPS_UNDOTS1
ADD
DATAFILE ‘/u12/oradata/testdata/rbs03.dbf’ SIZE 2000M;
Verify the newly created undo tablespace:
select tablespace_name, segment_name, status
from dba_rollback_segs;