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:
Post a Comment