Oracle 11g 10g 9i tablespace database undo
How to shrink the undo tablespace in Oracle?
We need to create a tablespace temporary so that we can redirect undo usage to this temperorary tablespace.
Steps:
1. Create a temporary tablespace for swapping
CREATE undo TABLESPACE undotbs2 DATAFILE
'/dbora01/oradata/ORCL/ora02/undotbs2_01.dbf' size 100M;
2. Use it as default UNDO tablespace
ALTER SYSTEM SET undo_tablespace=undotbs2;
3. Drop the old UNDO tablespace
DROP TABLESPACE undo including contents;
4. Create back the original UNDO tablespace
CREATE undo TABLESPACE undo DATAFILE
'/dbora01/oradata/ORCL/ora02/undo01.dbf' size 5000M REUSE,
'/dbora01/oradata/ORCL/ora03/undo02.dbf' size 5000M REUSE;
5. Set the default UNDO tablespace back to the original one
ALTER SYSTEM SET undo_tablespace=undo;
6. Drop the temporary one
DROP TABLESPACE undotbs2 including contents and datafiles;
7. End
No comments:
Post a Comment