How to check the size , archive and Purge the Audit Trail Table AUD$ in oracle database

# to Check the size of  AUD$ table:

SQL> select segment_name table_name ,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in (‘AUD$’);

# to  archive the table before truncate it :

Before deleting any rows you may want to archive the table data. You can achieve this by creating a table from  AUD$ and export it. Don’t export AUD$ directly.

1-create new table from the AUD$

SQL>CREATE TABLE AUDIT_archive TABLESPACE users as select * from SYS.AUD$;

2-  export the new table

3- Drop the table after  export operation

SQL> drop table AUDIT_archive

 

# Truncate audit table:

1.Checking the owner of aud$

SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where table_name=’AUD$’;

2.If the owner is SYSTEM, then run this command:

SQL> truncate table system.aud$;

if the owner is SYS, then run this command:

SQL> TRUNCATE TABLE sys.aud$;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s