Multi tool use

last time a table was accessed in oracle

Is it possible to determine when the last time a table was accessed in Oracle?
I am trying this, but this will not include when was the last time the table was selected.

select * from dba_objects;


3 Answers
3

From @KD 's answer I've developed this query to detect unused tables:

select a.obj#, b.object_name, b.owner, b.object_type, b.timestamp
from dba_hist_seg_stat a, dba_objects b
where a.obj# = b.object_id
and b.owner = 'YOUR_SCHEMA_NAME'
and object_name = 'A_TABLE_NAME'
order by timestamp desc


Since this query makes use of SYS user views, you must have special privileges to run it. If you have them, you can have a look at dba_hist_XXX views, or only have a look at the rest of the columns in the views used here: you have info about reads, writes, locks and many more.

EDIT: Thanks to @APC for the warning. DBA_HIST_XXX are views from Diagnostics Pack that require special license.

DBA_HIST_%

select p.object_owner owners, p.object_name Obj_Name, p.operation Operation,
p.options Options, count(1) Idx_Usg_Cnt
from dba_hist_sql_plan p,dba_hist_sqlstat s
where p.object_owner = '&USERNAME' and p.operation like 'TABLE%'
and p.sql_id = s.sql_id and p.object_name=’&OBJNAME’
group by p.object_owner,p.object_name,p.operation,p.options order by 1,2,3


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 'USERNAMES%'
order by table_scans_total desc


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.