Thursday, September 24, 2009

Table DML auditing to identify unused tables

# enable audit for tables with no indexes

spool audit.sql
select 'audit select, insert, update, delete on '||dt.owner||'.'||dt.table_name||';' from dba_tables dt
where dt.owner not in ('SYS','SYSTEM','DBSNMP','DVSYS','MDSYS','WMSYS','OLAPSYS','LBACSYS','XDB','EXFSYS','CTXSYS','WKSYS','ODM')
and not exists (select 1 from dba_indexes di where di.table_owner = dt.owner and di.table_name = dt.table_name)
and tablespace_name is not null
order by 1;
spool off;

# disable audit for tables which have been used.

select distinct 'noaudit select, insert, update, delete on '||dat.owner||'.'||dat.obj_name||';'
from dba_audit_trail dat where dat.action_name in ('SELECT','INSERT','UPDATE','DELETE')
and dat.timestamp > sysdate -1;

# List the tables that are audited, but not used

select owner, object_name
from dba_obj_audit_opts
where object_type = 'TABLE'
and owner not in ('DVSYS','LBACSYS','DVOWNER')
order by owner, object_name;