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 laterOracle 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 |