RE: Table last access date?
Date: Wed, 19 Nov 2008 09:44:29 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A903238863@usahm208.amer.corp.eds.com>
>> We want to see which tables haven't been accessed in a while to see
if they can be cleaned up <<
Tables that have not referenced used will not have SQL in the shared pool though you could build a list of everything that has been used and subtract that from what exists. Then I would use auditing to monitor access those tables on the list of potentially unused tables. Some might be used only monthly, quarterly, or even annually depending on your applications.
Something else you can check is the dependencies that exist via dba_dependencies. Some tables that are in fact not used may still be referenced in code that is used.
- Mark D Powell --
Phone (313) 592-5148
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of SHEEHAN, JEREMY
Sent: Wednesday, November 19, 2008 9:29 AM To: Jan-Hendrik.Boll_at_dataport.de; oracle-l_at_freelists.org Subject: RE: Table last access date? that's not a bad idea. I'll suggest that to my team. Someoneelse sent an email to me suggesting auditing, but I think that's overkill for the scope of what we want to do. We want to see which tables haven't been accessed in a while to see if they can be cleaned up.
Thanks!
Jeremy
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Jan-Hendrik.Boll_at_dataport.de
Sent: Wednesday, November 19, 2008 9:25 AM To: oracle-l_at_freelists.org Subject: AW: Table last access date? Hi, you could try to select * from v$sql where lower(sql_text) like'%table_name%'
The column LAST_LOAD_TIME will tell you when the statement has been stated.
Jan-Hendrik Boll
Von: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] Im Auftrag von SHEEHAN, JEREMY
Gesendet: Mittwoch, 19. November 2008 15:20 An: oracle-l Betreff: Table last access date? Does anyone have a way to find out the last time a table waslast accessed? It's something that needs to be figured out for my work and I wanted to know if anyone has a way to figure this out. If impossible or an incredible stretch just let me know!
Thanks!
Jeremy
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 19 2008 - 08:44:29 CST