My Oracle Support Banner

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

Last updated on NOVEMBER 28, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Cloud Infrastructure - Exadata Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Gen 2 Exadata Cloud at Customer - Version N/A and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

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

In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.