Sometimes
there will be situations where we run out of DB storage due to huge undo growth
and we wanted to shrink it to release some space for other datafiles in the
database and The best way to shrink Undo tablespace is to switch to a new Undo
tablespace and drop the old Undo tablespace
Create
New UNDOTBS2 tablespace
SQL>
create undo tablespace UNDOTBS2 datafile '+DATAC1' size 500M;
Tablespace
created.
SQL>
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;
System
altered.
SQL>
select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name,
status;
TABLESPACE_NAME
STATUS
COUNT(*)
------------------------------
---------------- ----------
SYSTEM
ONLINE
1
UNDOTBS1
OFFLINE
12
UNDOTBS2
ONLINE
10
If
there are Undo segments with a status other than OFFLINE in the tablespace to
be dropped, we need to wait till they become OFFLINE.
Check
any segments other than OFFLINE status using below
SQL>
select status,segment_name from dba_rollback_segs where status not in
('OFFLINE') and tablespace_name='UNDOTBS1';
no
rows selected
Since
there are no segments then we can easily drop the tablespace and release space
SQL> drop tablespace UNDOTBS1
including contents and datafiles;
Tablespace
dropped.
SQL>
select tablespace_name, status, count(*) from dba_rollback_segs group by
tablespace_name, status;
TABLESPACE_NAME
STATUS
COUNT(*)
------------------------------
---------------- ----------
SYSTEM
ONLINE
1
UNDOTBS2
ONLINE
10
UNDOTBS3
OFFLINE
22
Now
reset to same tablespace by creating same tablespace again
SQL>
create undo tablespace UNDOTBS1 datafile '+DATAC1' size 500M;
Tablespace
created.
SQL>
ALTER SYSTEM SET UNDO_TABLESPACE =UNDOTBS1 SCOPE=BOTH;
System
altered.
SQL>
select tablespace_name, status, count(*) from dba_rollback_segs group by
tablespace_name, status;
TABLESPACE_NAME
STATUS
COUNT(*)
------------------------------
---------------- ----------
SYSTEM
ONLINE
1
UNDOTBS1
ONLINE
15
UNDOTBS2
OFFLINE
10
Sometimes 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.
Also,
According to ORACLE DOC:
On some platforms, disk space is not
freed to the OS until the database is restarted. The disk space will
remain "allocated" from the OS perspective until the database
restart.
