SHRINK ROLLBACK SEGMENT AND RELEASE SPACE FROM UNDO TABLESPACE ORACLE
A 11g database got undo tablespace full due to autoextend and there is no space in disk to add any more datafile. At this point, there is no other options than to shrink the largest rollback segment and drop it. Go to the last page for solution 2.
While checking the number of expired and unexpired extents, total unexpired extents were too high
kIsH@x3z6zx9<^>def ucol="status,round(sum(bytes/1048576)) sizeMB,count(*) ucnt"
kIsH@x3z6zx9<^>def uview="dba_undo_extents"
kIsH@x3z6zx9<^>def ugrp="group by status"
kIsH@x3z6zx9<^>select &ucol
2 from &uview
3 &ugrp;
old 1: select &ucol
new 1: select status,round(sum(bytes/1048576)) sizeMB,count(*) ucnt
old 2: from &uview
new 2: from dba_undo_extents
old 3: &ugrp
new 3: group by status
STATUS SIZEMB UCNT
--------- ---------- ----------
UNEXPIRED 15 37
EXPIRED 9720 10238
One of the MV refresh job was consuming high space due to atomic refresh with delete statements. These statements which lock the undo files needs to be either released or killed to perform the housekeeping.
col SQL_TEXT for a20
col NOSPACEERRCNT for 999
col UNSC for 999
col ORA1555 for 999
select to_char(dhu.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,
2 to_char(dhu.END_TIME,'DD-MM-YY HH24:MI') ET,
dhu.MAXQUERYLEN as MQL,
dhu.MAXQUERYSQLID,
dhsq.SQL_TEXT,
dhu.TUNED_UNDORETENTION as TR,
dhu.ACTIVEBLKS as ABLK, --Retention not valid but space is valid
dhu.UNEXPIREDBLKS as UXBLK,
dhu.EXPIREDBLKS as XBLK,
3 4 5 6 dhu.NOSPACEERRCNT as ORA30036,
dhu.SSOLDERRCNT as ORA1555,
dhu.UNXPSTEALCNT as UNSC
from dba_hist_undostat dhu
inner join dba_hist_sqltext dhsq on (dhu.MAXQUERYSQLID = dhsq.sql_id)
order by dhu.MAXQUERYLEN desc; 7 8 9 10 11 12 13 14 15
BT ET MQL MAXQUERYSQLID SQL_TEXT TR ABLK UXBLK XBLK ORA30036 ORA1555 UNSC
-------------- -------------- ---------- ------------- -------------------- ---------- ---------- ---------- ---------- ---------- ------- ----
03-04-22 15:17 03-04-22 15:27 4945 6a7chgvffupxz delete from "KISH". 5786 563168 2832 16 0 0 344
"MVSAL"
03-04-22 15:07 03-04-22 15:17 4344 6a7chgvffupxz delete from "KISH". 5185 563168 2832 16 0 0 0
"MVSAL"
26-03-22 22:49 26-03-22 22:59 1651 4757g72w150w9 select owner, objec 2372 18216 1400 477472 0 0 0
t_name, partition_na
me, segment_type, t
ablespace_name, lobc
Top segments which occupy space is a rollback segment
kIsH@x3z6zx9<^>select * from (
select segment_name,
tablespace_name,
bytes/1073741824 GB
from dba_segments
order by GB desc)
where rownum < 6;
SEGMENT_NAME TABLESPACE_NAME GB
--------------------------------------------------------------------------------- -------------------- ----------
_SYSSMU7_2070203016$ UNDOTBS1 7.71105957
XTBL USERS 3.44116211
MVSAL USERS 1.87792969
YTBL USERS 1.81323242
ZTBL USERS 1.80493164
After drill down of the segment which is online, this needs to be dropped to release space
kIsH@x3z6zx9<^>select SEGMENT_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU11_1118192803$ ONLINE
_SYSSMU10_1197734989$ ONLINE
_SYSSMU9_1650507775$ ONLINE
_SYSSMU8_517538920$ ONLINE
_SYSSMU7_2070203016$ ONLINE <<<<==========
_SYSSMU6_1263032392$ ONLINE
_SYSSMU5_898567397$ ONLINE
_SYSSMU4_1254879796$ ONLINE
_SYSSMU3_1723003836$ ONLINE
_SYSSMU2_2996391332$ ONLINE
_SYSSMU1_3724004606$ ONLINE
12 rows selected.
Tried to shrink rollback segment but space didnot release.
kIsH@x3z6zx9<^>alter rollback segment "_SYSSMU7_2070203016$" shrink to 50M;
Rollback segment altered.
TBLSPC TOTAL_SPACE_MB TOTAL_FREE_SPACE PCT_F
-------------------- -------------- ---------------- ----------
UNDOTBS1 9740 5 0
Kept the rollback segment offline
kIsH@x3z6zx9<^>alter rollback segment "_SYSSMU7_2070203016$" offline;
Rollback segment altered.
Now the only option is to drop the segment. Added the large rollback segment to the offline list and dropped the segment. This needs a bounce before drop.
kIsH@x3z6zx9<^>alter system set "_OFFLINE_ROLLBACK_SEGMENTS"="_SYSSMU7_2070203016$" scope=spfile;
System altered.
--Bounce the database
kIsH@x3z6zx9<^>drop rollback segment "_SYSSMU7_2070203016$";
Rollback segment dropped.
Space has been released
TBLSPC TOTAL_SPACE_MB TOTAL_FREE_SPACE PCT_F
-------------------- -------------- ---------------- ----------
UNDOTBS1 9740 7901 81
USERS 10196 4192 41
EXAMPLE 368 55 15
TBSPC_INDX 600 40 7
SYSAUX 650 33 5
SYSTEM 840 12 1
6 rows selected.
STATUS SIZEMB UCNT
--------- ---------- ----------
UNEXPIRED 15 37
EXPIRED 1824 3235
Another efficient solution is to create a new undo tablespace and drop the existing one. This doesn’t require bounce.
kIsH@x3z6zx9<^>create undo tablespace UNDOTBS3 datafile '+DATAX' size 5M autoextend on next 10M maxsize 30000M;
Tablespace created.
Elapsed: 00:00:00.60
kIsH@x3z6zx9<^>alter system set undo_tablespace=UNDOTBS3;
System altered.
Elapsed: 00:00:00.15
kIsH@x3z6zx9<^>drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:01.63