Cleanup steps for Oracle FGA Audit Records

Note: This article is original article, reproduced please indicate the source:
http://blog.csdn.net/msdnchina/article/details/38435999
First, confirm what FGA audit strategy,
From the SELECT * from dba_audit_policies you can query the audit policy, note that each row in the query results is an audit policy.

In addition, FGA Audit records are stored in this view: SELECT * from Dba_fga_audit_trail
The corresponding background base table for this view is: sys.fga_log$, see the following view. Note that the FGA audit policy is not stored in the sys.aud$ table!!!

---Excerpt start create OR REPLACE VIEW dba_fga_audit_trail (session_id, timestamp, Db_user, Os_user, Userhost, client_id, Econtext _id, Ext_name, Object_schema, object_name, Policy_name, SCN, Sql_text, Sql_bind, Comment$text, Statement_type, Extended_ Timestamp, Proxy_sessionid, Global_uid, Instance_number, os_process, TransactionID, Statementid, EntryID, Obj_edition_  Name, dbid) Asselect SessionID, CAST ((From_tz (ntimestamp#, ' 00:00 ') at LOCAL) as DATE), Dbuid,      Osuid, Oshst, ClientID, Auditid, Extid, Obj$schema, Obj$name, PolicyName, SCN, To_nchar (substr (lsqltext,1,2000)), To_nchar (substr (lsqlbind,1,2000)), Comment$text, DECODE (Stmt_type, 1, ' SELECT ', 2, ' INSERT ', 4, ' UPDA      TE ', 8, ' DELETE ', ' INVALID '), From_tz (ntimestamp#, ' 00:00 ') at LOCAL, Proxy$sid, User$guid, instance#, process#, XID, statement, EntryID, obj$edition, Dbidfrom sys.fga_log$---excerpt ends

Second, disable FGA Audit policy:

The following is an example of Policy_name = ' emp_fga_audit ', and of course, there may be multiple audit strategies in the customer's environment.

Begindbms_fga.enable_policy (  object_schema = ' SCOTT ',   object_name = ' EMP ',  policy_name = ' Emp_fga_audit ',  enable + = FALSE); end;

Please complete the other audit strategy

Third, implement EXP export sys.fga_log$ table

Exp SYS/AA file=fga_logdmp log=fga_logexp.log tables=sys.fga_log$ buffer=99999999

Note: sys.fga_log$ is 5G in size (note that the table has LOB fields and cannot only query dba_segments to determine the size of the table with LOB columns), so the export may take a while.

Iv. Deleting log tables:

TRUNCATE TABLE sys.fga_log$

V. Open FGA Audit Strategy:

Begindbms_fga.enable_policy (  object_schema = ' SCOTT ',   object_name = ' EMP ',  policy_name = ' Emp_fga_audit ',  enable = TRUE); end;

Please complete the other audit strategy

Note: The top and the back information can be queried from the dba_audit_policies.