Procedure (Unified Auditing; 12c and later)
1) Verify/enable Unified Auditing view availability
- Test access:
select count(*) from unified_audit_trail;
- If it errors, you are likely in pre-12c or Unified Auditing not available; stop and clarify version.
2) Create a DML auditing policy for target tables
- For a specific table:
create audit policy app_dml_pol actions
insert on APP_SCHEMA.T1,
update on APP_SCHEMA.T1,
delete on APP_SCHEMA.T1;
audit policy app_dml_pol;
- For many existing tables (generate statements):
select 'alter audit policy app_dml_pol add actions insert, update, delete on '
|| owner || '.' || table_name || ';'
from dba_tables
where owner = 'APP_SCHEMA';
-- run the generated ALTER AUDIT POLICY statements
3) Automatically include future tables (workaround via DDL trigger; no schema-wide wildcard)
- One-time setup:
create or replace trigger app_schema_audit_ddl
after create on database
declare
v_stmt varchar2(4000);
begin
if ora_dict_obj_type = 'TABLE' and ora_dict_obj_owner = 'APP_SCHEMA' then
v_stmt := 'alter audit policy app_dml_pol add actions insert, update, delete on '
|| ora_dict_obj_owner || '.' || ora_dict_obj_name;
execute immediate v_stmt;
end if;
end;
/
-- Ensure the policy app_dml_pol exists before creating the trigger.
4) (Optional) Limit auditing to application users to reduce volume
- Example:
noaudit policy app_dml_pol;
audit policy app_dml_pol by users APP_USER1, APP_USER2;
5) Review/Report audited DML
- Recent DML counts by object and action:
select to_char(event_timestamp, 'YYYY-MM-DD HH24:MI') ts_min,
obj_name,
action_name,
count(*) cnt
from unified_audit_trail
where dbusername in ('APP_USER1','APP_USER2') -- or remove to see all
and action_name in ('INSERT','UPDATE','DELETE')
and event_timestamp >= systimestamp - interval '1' day
group by to_char(event_timestamp, 'YYYY-MM-DD HH24:MI'), obj_name, action_name
order by 1,2,3;
- Who/where summary:
select obj_name, action_name, dbusername, client_id, userhost, module,
count(*) cnt
from unified_audit_trail
where action_name in ('INSERT','UPDATE','DELETE')
and event_timestamp >= systimestamp - interval '1' day
group by obj_name, action_name, dbusername, client_id, userhost, module
order by cnt desc;
6) Purge/retention (prevent trail growth and performance impact)
- Create a purge job retaining 30 days (adjust as needed):
begin
dbms_audit_mgmt.init_cleanup(audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
default_cleanup_interval => 24);
dbms_audit_mgmt.create_purge_job(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'PURGE_UNIFIED_AUDIT_30D',
use_last_arch_timestamp => true);
dbms_audit_mgmt.set_last_archivelog_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
last_archive_time => systimestamp - interval '30' day);
end;
/
7) Disable if needed
- Stop auditing:
noaudit policy app_dml_pol;
- Remove objects from policy or drop policy when finished:
drop audit policy app_dml_pol;
Notes
- Auditing every DML on a large schema can add overhead; start with the most-suspect tables/users, then expand as required.
- For SYS activity (if relevant), enabling audit_sys_operations captures privileged operations; use only if needed.
- If fixed object stats jobs slow down due to a very large unified audit trail, lock stats on SYS.X$UNIFIED_AUDIT_TRAIL:
exec dbms_stats.lock_table_stats('SYS','X$UNIFIED_AUDIT_TRAIL');
Summary: Yes. Use Unified Auditing to audit INSERT/UPDATE/DELETE on the application tables. Create a policy, attach target tables (generate statements for many tables), optionally auto-add future tables with a DDL trigger, restrict to app users if desired, report from UNIFIED_AUDIT_TRAIL, and manage retention with DBMS_AUDIT_MGMT.
