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

Leave a Reply