Stopping Auditing of Objects

Assume you have set-up auditing of tables in the following 3 schemas:

FINANCE1, DATAUSER2 and REPORTUSER9

The purpose of the auditing is to check whether any of the schemas' tables is no longer being used i.e. not queried or changed in any way.

Any unused tables in these schemas will be dropped as part of housekeeping.

You want to stop AUD$ becoming too large, and so want to stop auditing of a table soon after any rows for it are written to AUD$.

You have set-up auditing of the 3 schemas' table by executing the SQL generated by the following dynamic SQL:

select distinct 'AUDIT SELECT, INSERT, UPDATE, DELETE ON "'||owner||'"."'||table_name||'" BY SESSION;'
from dba_tables
where owner in ('FINANCE1','DATAUSER2','REPORTUSER9');
 

If the following dynamic SQL is executed it will generate a NOAUDIT SQL statement for each table in the 3 schemas that is being audited, and which has has at least 1 row in AUD$:

SELECT  DISTINCT 'NOAUDIT INSERT, UPDATE, DELETE, SELECT ON '||OBJ$CREATOR||'.'||OBJ$NAME||';'
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE)
AND OBJ$CREATOR||'.'||OBJ$NAME IN (SELECT OWNER||'.'||OBJECT_NAME FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER IN ('FINANCE1','DATAUSER2','REPORTUSER9') AND OBJECT_TYPE='TABLE');

Note:
If your AUD$ is large, then you may want to index AUD$