CODEWITHSUNDEEP
oracle-databaseaudit-trail
Giles
Giles

Reputation: 51

Purging Oracle Unified Audit Trail doesn't cleanup lob data

I'm experiencing rapid growth in my SYSAUX scheme. I have found that the majority of the space (27Gb) is being consumed by a LOBSEGMENT object in the AUDSYS schema. The research I did, suggested that the Unified Audit Log needed to be purged and I went ahead and cleaned it up as it was really massive, however, space has not been released from the LOBSEGEMENT and I'm wondering if there is a way to do this?

DB Version: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

I used the below to identify large objects in the system

select s.owner, s.segment_name, s.segment_type, s.tablespace_name, sum(s.BYTES) /1024/1024/1024 SIZE_GB 
from DBA_SEGMENTS s
group by s.owner, s.segment_name, s.segment_type, s.tablespace_name;

From there I identified the table name associated with the largest segment with the below:

select * from dba_lobs where SEGMENT_NAME='SYS_LOB0000019764C00014$$';

The LOG_PIECE column of the AUDSYS.CLI_SWP$ea27aff$1$1 table was identified, but I cannot query the table directly. even connected with sysdba, when I try and query the table to find out what is in it, I get "ORA-00942: table or view does not exist". I also cannot find any reference to the table or column in any other views, procedures, synonyms, etc in the DB. So I have no idea how to view the contents of the table in order to figure out what it is.

When I look at the Unified Audit Trail, I can't find anything that would link to this column either.

After purging I did another backup of the system in the hopes that it might release unused space, space is still being used and the purge did not clean it up.

Any ideas on

  1. How to figure out what is in the table/column
  2. how to clean it up would really be appreciated as I'm at a bit of a loss here.

Upvotes: 1

Views: 2958

Answers (0)

Related Questions