Sunday, November 18, 2007

How To Shrink Undo Tablespace

If you want to shrink the Undo Tablespace ,you will shrink the Datafile to free disk space.

1-you will need to create another Tablespace with a smaller Datafile size
SQL> create undo tablespace UNDO_TBS1
datafile 'd:\oracle\oradata\orcl\undorbs1.dbf' size 100m;

2-Let the new tablespace to be the default Undo Tablespace
SQL> alter system set undo_tablespace=UNDO_TBS1;

3-Check that there's no active Undo Segments in your Database using this query
SQL> SELECT a.name,b.status FROM v$rollname a, v$rollstat b WHERE a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBS0') AND a.usn = b.usn;
if the query returns no rows then there's no active Undo segments

4- Drop the old Tablespace
SQL> drop tablespace UNDO_TBS0 including contents.

No comments: