What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table.

A list of defined policies can be obtained from DBA_AUDIT_POLICIES

Audit records are stored in DBA_FGA_AUDIT_TRAIL

Example:

Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

SQL>analyze table EMP compute statistics;
SQL>select * from EMP where c1 = 11; -- Will trigger auditing
SQL>select * from EMP where c1 = 09; -- No auditing

Now we can see the statements that triggered the auditing condition...
SQL>select sqltext from sys.fga_log$;
SQL>delete from sys.fga_log$;