High Storage Consumption for LOBs in SYSAUX Tablespace (Doc ID 396502.1)

Last updated on JULY 02, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 02-Jul-2016***



Symptoms

A number of LOB segments created for LOB columns of AWR tables in the SYSAUX tablespace consume significant storage.

This storage is not released even after AWR snapshots are dropped and row counts in the tables reduced.

An example is the CLOB for the SQL_TEXT column of the AWR table WRH$_SQLTEXT.

In the output shown below, the LOB segment has grown to 300Mb and remains so even after snapshots have been dropped and the row count in its table reduced to 2981.

SQL> select * from dba_lobs where table_name = 'WRH$_SQLTEXT';

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS
------------------------------ ---------- ---------- ---------- ----------
CACHE LOGGING IN_ FORMAT PAR
---------- ------- --- --------------- ---
SYS WRH$_SQLTEXT
SQL_TEXT
SYS_LOB0000008944C00004$$ SYSAUX
SYS_IL0000008944C00004$$ 8192 900
NO YES YES ENDIAN NEUTRAL NO

SQL> select * from dba_segments where segment_name = 'SYS_LOB0000008944C00004$$';

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS INITIAL_EXTENT
----------- ------------ ---------- ---------- ---------- --------------
NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
----------- ----------- ----------- ------------ ---------- ---------------
RELATIVE_FNO BUFFER_
------------ -------
SYS
SYS_LOB0000008944C00004$$
LOBSEGMENT SYSAUX
3 2979 309329920 37760 110 65536
1 2147483645
3 DEFAULT

SQL> select count(*) from WRH$_SQLTEXT;

   COUNT(*)
----------
      2981

Changes

The storage in the SYSAUX tablespace taken for AWR tables may have been larger than usual due to increased workload, increased frequency for AWR snapshot collection or a longer AWR retention period.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms