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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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

Whilst you are here, check out some content from the AskTom team: PLSQLville: Sung to the tune of Margaritaville

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 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

Reviews

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 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

Followup  

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