I have been tasked with identifying which of my group's schema objects (tables and views) are not being used anymore. What system table(s) can I query to find out which users are running SELECT statements against a specific owner's schema objects, preferably, with a datetime stamp? I am not a DBA, but I do create and update tables for reporting. Our DBAs have not been able to provide me with this data.
A nice easy starting point is DBA_TAB_MODIFICATIONS. It is kept up to date every 15mins (or you can manually flush the info - you'll see in the demo below)
SQL> create table t as select * from dba_objects;
Table created.
SQL>
SQL> select
2 inserts
3 ,updates
4 ,deletes
5 ,timestamp
6 from dba_tab_modifications
7 where table_name = 'T'
8 and table_owner = user;
no rows selected
SQL>
SQL> delete from t where rownum <= 10;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> update t set owner = lower(owner) where rownum <= 50;
50 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 inserts
3 ,updates
4 ,deletes
5 ,timestamp
6 from dba_tab_modifications
7 where table_name = 'T'
8 and table_owner = user;
INSERTS UPDATES DELETES TIMESTAMP
---------- ---------- ---------- ---------
0 50 10 23-FEB-19
1 row selected.
Now one important thing to note is that this data is really used by optimizer jobs to work out which tables are 'stale', so once you gather stats (typically a nightly job) then the data is erased.
SQL> exec dbms_stats.gather_table_stats('','T')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select
2 inserts
3 ,updates
4 ,deletes
5 ,timestamp
6 from dba_tab_modifications
7 where table_name = 'T'
8 and table_owner = user;
no rows selected
SQL>
But that's a zero cost way of checking for DML on tables.
That doesn't cover SELECT statements. For that, you can query v$sql_plan or dba_hist_sql_plan at regular intervals to get a distinct list of object names.
Using those two methods, you can get a list of objects that you anticipate are in regular use. For those objects that are NOT in that list, now you probably want greater assurance that they are not being used. Once you get to this point, I'd look at using auditing, ie
audit insert, update, delete, select on MY_CANDIDATE_TABLE by access;
and leave that in place for a while to get greater confidence.
One thing to remember - its not uncommon for certain objects in a database to be accessed very rarely (eg yearly reports etc etc), so you always need to tread with caution before dropping objects.
For tables - consider renaming rather than dropping
For indexes - consider making invisible rather than dropping
And finally (because I just remembered :-)), don't forget that you can look at v$segment_statistics as wel