How to check audit trail size and flush or truncate audit trial?

2.4k views Asked by At

We have Oracle database in our startup company and there is only 1GB space is left in the entire database, It will be occupied soon so What should be done to make space in database?

Can we truncate or delete Audit trail records in the database? If so how to do it? Any other tips to make space in the database?

1

There are 1 answers

1
Barbaros Özhan On BEST ANSWER

it seems your

audit_trail

parameter is set to

DB
Write the standard audit content to sys.aud$ table

or

DB, EXTENDED 
Write standard audit content to sys.aud$ along
with the SQL text and bind variable content that
was executed for that SQL

you should write the content to an OS file by setting

audit_trail

to one of below ones :

OS 
Write the standard audit content to text files

XML 
Write the standard audit content and FGA ( Fine Grained Auditing ) audit
content to an XML formatted file 

XML, EXTENDED 
Write the standard audit content and FGA content
to an XML formatted file along with SQL text and
bind variable content. 

As an example, you may issue this command :

SQL> alter system set AUDIT_TRAIL=xml, extended scope=spfile;

and after that, you need to restart db.

i forgot to tell about truncating, ok you may take backup of sys.aud$ table and then you may truncate in classical manner : truncate table sys.aud$