Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Kathleen.

Asked: February 22, 2019 - 8:06 pm UTC

Last updated: March 29, 2022 - 7:41 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

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.

and Connor said...

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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sys.col_usage$

David D., March 01, 2019 - 9:56 am UTC


Hello Tom,

What do you think about using sys.col_usage$ to know which objects were used? After that, we can do a MINUS to have the objects not used since ... well, I don't know but it could be interesting, no?

SQL> select distinct O.owner, O.object_name, C.obj#
from sys.col_usage$ C, DBA_OBJECTS O
where C.obj#=O.object_id and
O.owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN')
order by O.owner, O.object_name;
Connor McDonald
March 06, 2019 - 6:45 am UTC

Certainly, but don't forget that it too will be regularly cleared.

And don't forget v$segment_statistics as well (I should have mentioned in this in the original answer)

Need details of who is using what tables in specific DB schema

Paul, March 29, 2022 - 3:47 am UTC

Thanks for the keeping us learning a lot from this site.

Database is on 19.11.

I have below requirement from application and hope you can provide some ideas on the query to come up with.

1. How do I find out which service accounts (database user accounts) are accessing or performing (SELECT/INSERT/UPDATE/DELETE/TRUNCATE/MERGE) tables in 2 specific schemas.

Note that, I have to list the table name as well as other object names (if service account those tables indirectly via views/package/procedure/function)

2. In addition to it, How often (number of times) the service account access those tables/objects for the last month or given specific time range.

Thanks!

Chris Saxon
March 29, 2022 - 7:41 am UTC

To be sure of capturing this information, really you need to enable auditing.

ASH is based on samples and AWR doesn't store the full history of all statements, so you can't guarantee to be able to get it from these.

Continuation of previous post

Paul, March 29, 2022 - 3:53 am UTC

I forgot to mention that, we do not have auditing at the table level enabled. If its possible, can we get this info from ASH/AWR Historical views.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.