RE: Table last access date?

From: Powell, Mark D <mark.powell_at_eds.com>
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.  Someone
else 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 was
last 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-l
Received on Wed Nov 19 2008 - 08:44:29 CST

Original text of this message