High Space Usage for SecureFile LOB column of table AUDIT_DETAILS - SOA cloud service
(Doc ID 2441281.1)
Last updated on FEBRUARY 04, 2019
Applies to:
Oracle Database Cloud Service - Version N/A to N/A [Release 1.0]Information in this document applies to any platform.
Symptoms
The AUDIT_DETAILS table contain LOB column BIN. A huge space usage is observed in the SecureFile LOB segment.
The space occupied is due to the expired extents. This is confirmed by the PL/SQL output and SQL query output from dba_segments
Exemple: (check_space_securefile is extracted from note 1453350.1)
SQL> select table_name, column_name,pctversion,retention,securefile from dba_lobs where owner = 'SP652493798_SOAINFRA' and segment_name='SYS_LOB0000099241C00006$$';
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC
---------- ---------- --- ------------------------
AUDIT_DETAILS BIN 0 YES
SQL> select owner,bytes/(1024*1024*1024) segment_type from dba_segments where owner=<owner> and segment_name='SYS_LOB0000099241C00006$$';
OWNER BYTES/(1024*1024*1024) SEGMENT_TYPE
--------------------------------------------------------------------------------
SP652493798_SOAINFRA 32.0490112 LOBSEGMENT
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC
---------- ---------- --- ------------------------
AUDIT_DETAILS BIN 0 YES
SQL> select owner,bytes/(1024*1024*1024) segment_type from dba_segments where owner=<owner> and segment_name='SYS_LOB0000099241C00006$$';
OWNER BYTES/(1024*1024*1024) SEGMENT_TYPE
--------------------------------------------------------------------------------
SP652493798_SOAINFRA 32.0490112 LOBSEGMENT
SQL> execute check_space_sf;
Segment Blocks = 4200728 Bytes = 34412363776
Used Blocks = 55877 Bytes = 457744384
Expired Blocks = 4139698 Bytes = 33912406016
Unexpired Blocks = 0 Bytes = 0
Segment Blocks = 4200728 Bytes = 34412363776
Used Blocks = 55877 Bytes = 457744384
Expired Blocks = 4139698 Bytes = 33912406016
Unexpired Blocks = 0 Bytes = 0
Most of the space occupied by the Expired Blocks.
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 |
| Cause |
| Solution |
| References |