Skip to Main Content
  • Questions
  • When was the last time, a table was accessed


Question and Answer

Tom Kyte

Thanks for the question, Anto.

Asked: April 30, 2010 - 12:22 pm UTC

Answered by: Tom Kyte - Last updated: May 06, 2010 - 1:56 pm UTC

Category: Database - Version: 10g

Viewed 10K+ times! This question is

You Asked


Is there any way, one can find out in a database(with NO auditing enabled and all settings are default) when a particular table was last used ?(by used - I mean any 'SELECT' or DML operation was done on the table).

Other than 'auditing' do we have any other option to find out this ?


and we said...

Auditing of course would be the easiest way

You could possibly get something from the DBA_HIST views (part of AWR/ASH - workload repository and active session history) but bear in mind

o they are sampled snapshots - not 100% of everything is in there
o many of them are "top" reports only - dba_hist_seg_stat for example - shows IO's against segments - it seems perfect, but it is only "This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT" - it will not have 100% of everything.

Auditing is the right answer, funny thing with auditing - we only really want it when it isn't on don't we :)

and you rated our response

  (2 ratings)

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


April 30, 2010 - 3:09 pm UTC

Reviewer: Anto

is this correct

May 05, 2010 - 5:57 am UTC

Reviewer: A reader

I'm using the SQL below to find the list of segments getting full table scaned and how many times they have been full table this SQL is correct with respect to this.

select a.obj#,a.table_scans_delta,b.object_name,b.owner,b.object_type
from dba_hist_seg_stat a, dba_objects b
where a.obj# = b.object_id
and b.owner like 'JAROS%'
order by table_scans_total desc
Tom Kyte


May 06, 2010 - 1:56 pm UTC

as long as you understand the dba_hist views are samples in general - so the number might not be *exactly* dead on - but closer than close enough...