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

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Anto.

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

Last updated: May 06, 2010 - 1:56 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Tom,

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 ?

thanks
Anto

and Tom 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 :)

Rating

  (2 ratings)

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

Comments

Anto, April 30, 2010 - 3:09 pm UTC


is this correct

A reader, May 05, 2010 - 5:57 am UTC

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 scanned..is 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...

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.