After years of changes made to an in-house system, it became very hard for our developers to identify which tables are no longer used. Going through hundreds of thousands of line of code and cross referencing hundreds of tables manually is not a simple task. So, and like usual, send it over to the DBA team.
I’m must admit it wasn’t a stimulating task in the beginning, but once we figured we can use fine grained auditing (FGA), it became more exciting.
Here is the procedure we used, we have only 1 main schema:
1) Identify tables with DML. This is easily done by checking user_tab_modifications.
2) Create a table unused_tables containing all tables not in user_tab_modifications. With this step, you would eliminate some of the tables currently being used (hopefully most of them):
create table unused_tables as
Select table_name from user_tables
Select table_name from user_tab_modifications
3) Enable FGA for “selects” only on the tables in previous step. FGA puts overhead on your system so don’t enable it on all your tables at once if you have thousands of tables. Enable few say 50 or use you best judgment at a time.
# generate script to enable auditing
‘exec dbms_fga.add_policy(object_schema => ”your_schema”, object_name => ”’ || table_name || ”’, policy_name => ”’ ||
table_name || ‘_P”);’
4) Look under DBA_FGA_AUDIT_TRAIL and disable policies for tables that have a trail. Here is script we use to disable policies, we run about once a day:
# generate script to disable auditing
select ‘exec dbms_fga.disable_policy(object_schema => ”’ || object_schema || ”’ , object_name => ”’ || object_name ||
”’, policy_name => ”’ || policy_name || ”’);’
select distinct a.object_schema, a.object_name, a.policy_name
from DBA_FGA_AUDIT_TRAIL a, DBA_AUDIT_POLICIES b
where a.object_schema = b.object_schema
and a.object_name = b.object_name
and a.policy_name = b.policy_name
and b.enabled = ‘YES’
5) Repeat steps 3 & 4 until you enable auditing for all tables
6) Tables with enabled policies and no audit trail are these tables you can safely drop. Off course you have to wait an X amount of time (weeks most probably) to confirm.
7) Be aware that if you do a nightly dump (export), it will show in the audit trail. We normally delete these entries from the audit trail. Our export is done with a different user then the schema owner:
delete from sys.fga_log$ where dbuid = ‘export user’;
Senior Oracle DBA