Problem: ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM

This error generally results when the aud$ table has a next_extent value that is greater than the available contiguous space in the SYSTEM tablespace. The aud$ table records logins to the database so when this error occurs, new database user connections may error. The simple fix to immediately relieve the hang on the database is to add more space to the SYSTEM datafiles. However, the long-term fix is to analyze the next_extent size to ensure it is reasonable and it would also be a good practice to move the audit tables out of the SYSTEM tablespace and into an auxiliary tablespace or create a dedicated tablespace sized appropriately for the audit tables. Note that in version 12c and higher Oracle introduced unified auditing which stores data differently than in 11g. This blog goes over the Oracle RDBMS version 11g solution.

This blog will cover the steps and scripts to view the present configuration of the AUD$ table in 11g, how to move the AUD$ table to another tablespace outside of the SYSTEM tablespace and how to set up a purge job to continuously purge records for the audit trail every 24 hours.

Disclosure: The data displayed in these examples are from a test database for example purposes only. And in no way reflect an actual production system. Please do not rely on your results to match this test data.

The first step is to document the present configuration for the AUD$ table and the audit trail configuration details:

set lines 300
col owner for a10
col segment_name for a12
col segment_type for a10
col tablespace_name for a12
col SEGMENT_SUBTYPE for a10
 select owner, segment_name, segment_type,SEGMENT_SUBTYPE, tablespace_name, bytes/1024/1024 from dba_segments
where segment_name IN ('AUD
, 'FGA_LOG
)
 
OWNER  	SEGMENT_NAME SEGMENT_TY SEGMENT_SU TABLESPACE_N BYTES/1024/1024
---------- ------------ ---------- ---------- ------------ ---------------
SYS    	AUD$     	TABLE  	MSSM       SYSTEM        	21504.44
SYS	   FGA_LOG$	  TABLE      MSSM   	SYSAUX   	 	.0625
 
col owner for a20
select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name IN ('AUD
, 'FGA_LOG
)
 
LAST_ANAL OWNER                TABLE_NAME                       NUM_ROWS
--------- -------------------- ------------------------------ ----------
01-DEC-18 SYS                  AUD$                  	        10,704,439.5
   	  SYS     	   	 FGA_LOG$
 
PROMPT the aud$ table in 11g contains two CLOB columns sqlbind and sqltext
select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name IN ('AUD
, 'FGA_LOG
)
and owner='SYS';
OWNER	            INDEX_NAME                 	INDEX_TYPE              	LAST_ANAL
-------------------- ------------------------------ --------------------------- ---------
SYS                  SYS_IL0000000090C00040$   	LOB
SYS                  SYS_IL0000000090C00041$   	LOB
SYS       	 	 SYS_IL0000106265C00013$   	LOB
SYS       	 	 SYS_IL0000106265C00028$       LOB
 
 
set line 200
col column_name format a30
col segment_name for a40
col table_name for a10
SELECT table_name, column_name, segment_name, a.bytes/1024/1024/1024 Gbytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name IN ('AUD
, 'FGA_LOG
);
 
TABLE_NAME COLUMN_NAME          	  SEGMENT_NAME                 	   	GBYTES
---------- ------------------------------ ---------------------------------------- ----------
AUD$      SQLTEXT              	  SYS_LOB0000000501C00041$      	   .000061035
AUD$      SQLBIND              	  SYS_LOB0000000501C00040$      	   .000061035
FGA_LOG$   LSQLBIND             	  SYS_LOB0000106265C00028$      	    .00012207
FGA_LOG$   LSQLTEXT             	  SYS_LOB0000106265C00013$      	    .00012207
 
PROMPT Tablespaces holding audit data
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD
, 'FGA_LOG
)
ORDER BY table_name;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSTEM
FGA_LOG$                       SYSTEM
 
select count(*) from sys.AUD$;
  COUNT(*)
----------
 18,144,731.5
 
select count(*) from sys.FGA_LOG$;
  COUNT(*)
----------
     	0
 
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME                 PARAMETER_VALUE  	AUDIT_TRAIL
------------------------------ -------------------- ----------------------------
DB AUDIT TABLESPACE            SYSAUX           	STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX           	FGA AUDIT TRAIL
AUDIT FILE MAX SIZE            10000            	OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000            	XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                	OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                	XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000            	STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000            	FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000             	OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000             	XML AUDIT TRAIL
 
SELECT * FROM dba_audit_mgmt_last_arch_ts;
no rows selected
select min(ntimestamp#) from sys.aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
28-JAN-19 12.20.33.754071 PM

From the above details, we can see that the AUD$ table is currently contained in the SYSTEM tablespace and consumes 21 GB of space. There is no “last archive timestamp” set. And all the auditing parameters are still set as the defaults. Additionally, this table has not been analyzed since 2018 and either has the two LOB segments attached to it.

The next step is to move the AUD$ SYS owned table out of the SYSTEM tablespace and into a different tablespace, such as SYSAUX.

The script below will create the new AUD$ table in a different tablespace. We do not need to change the user, only the tablespace.

PROMPT We want to lower the next_extent value to 50 – 100 mb
Alter table sys.aud$ storage(next 50m);
 
PROMPT These two scripts moves table AUD$ and FGA$, change the value SYSAUX if you have a user defined tablespace
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'SYSAUX');
END;
/
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'SYSAUX');
END;
/
 
set lines 300
col owner for a10
col segment_name for a12
col segment_type for a10
col tablespace_name for a12
col SEGMENT_SUBTYPE for a10
select owner, segment_name, segment_type,SEGMENT_SUBTYPE, tablespace_name,bytes/1024/1024 from dba_segments
where segment_name='AUD
;

The last step is to create a regular purge job so all records over 1 year are continuously purged daily.

 
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
 
BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
	AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    DEFAULT_CLEANUP_INTERVAL => 24);
END;
/
 
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
 
PROMPT Partial Purge using LAST ARCHIVE TIMESTAMP
CREATE OR REPLACE PROCEDURE AUD_SET_LAST_ARCH_TS
AS
  retention NUMBER;
BEGIN
  retention := 365 /* days */;
   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
   );
END;
/
 
PROMPT This command will create a job that uses the timestamp procedure AUD_SET_LAST_ARCH_TS
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB (
    	job_name => 'JOB_SET_LAST_ARCH_TS',
    	schedule_name => 'SYS.MAINTENANCE_WINDOW_GROUP',
    	job_class => 'DEFAULT_JOB_CLASS',
    	job_type => 'PLSQL_BLOCK',
    	job_action => 'BEGIN AUD_SET_LAST_ARCH_TS(); END;',
    	comments => 'Job to purge all audit trails'
	);
    SYS.DBMS_SCHEDULER.ENABLE(name => 'JOB_SET_LAST_ARCH_TS');
END;
/
 
PROMPT This command will create the purge job
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
    	audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        audit_trail_purge_interval => 24,
        audit_trail_purge_name => 'JOB_PURGE_AUDIT_TRAIL',
        use_last_arch_timestamp => TRUE
	); 
END;
/
 
PROMPT This command will enable the job so it starts running
BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  audit_trail_purge_name  	=> 'JOB_PURGE_AUDIT_TRAIL',
  audit_trail_status_value	=> DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/
 
PROMPT displays job status for audit trail
col job_name for a30
col job_frequency for a30
 select * from DBA_AUDIT_MGMT_CLEANUP_JOBS
 
 
PROMPT displays information about the history of audit trail cleanup or purge events.
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
Share This