Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.3
This problem can occur on 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
Many of the WRH$ tables storing AWR data are partitioned. When AWR snapshots are dropped or automatically purged after the retention period passes, the data in such tables is removed by dropping whole partitions. Dropping partitions also drops whole LOB segments belonging to them.
In other tables e.g. WRH$_SQLTEXT, which are not partitioned, rows are deleted when AWR snapshots are dropped or purged. Therefore any LOB segments on such tables are not dropped and LOB data is simply deleted from them.
This means that storage allocated to such LOB segments will never be released and they will remain with their highest number of extents allocated, even when most of the data stored in them has been deleted.
Solution
The LOB segments can be rebuilt using the same name, storage parameters and SYSAUX tablespace by executing a MOVE operation on them. This builds a new LOB segment containing only the data currently in the LOB and exchanges it with the original LOB segment.
To implement the solution, please execute the following steps:
1. Startup the database in RESTRICT mode to prevent interference from AWR operations during the rebuild.
2. From a SQL*Plus session connected as SYSDBA perform the move operation as follows:
SQL> alter table wrh$_sqltext move lob(sql_text) store as tablespace sysaux;
in this example the AWR table is WRH$_SQLTEXT, the LOB column is SQL_TEXT and
is the name of the LOB segment whose storage we want to reclaim e.g. SYS_LOB0000008944C00004$$
The new LOB segment will have a new data_object_id in dba_objects and a new (header_file,header_block) pair in dba_segments. It should also have a reduced number of bytes and blocks in dba_segments.
3. Check for and rebuild any unusable index on the table after the LOB move:
SQL> select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';
INDEX_NAME
------------------------------
WRH$_SQLTEXT_PK
SQL> alter index WRH$_SQLTEXT_PK rebuild;
Index altered.
4. Finally, restart the database in normal mode.
