Re: Table last access date?

Hi Jeremy,

How about using COL_USAGE$. I wrote up a reply oh-so long ago on a slightly different question. See this link:

http://www.freelists.org/post/oracle-l/re-CBO-A-Configuration-Roadmap-Histograms-on-NonIndexed-Columns,6

I wrote this to figure out to determine which columns are used in predicates. You can easily adopt this SQL to determine if some SQL statement with columns from this was parsed. The timestamp will show when any SQL that used that table was last hard parsed.

Something to check: Better than using V$SQL (which could get flushed) or STATSPACK (if this was not high up in Top SQL, it may not be caught). The only caveat? If there was no WHERE clause in SQLs against that table, it will not show up. However, this is a remote possibility as most SQL will have some form of filter or access predicates.

--

John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **
--

http://www.freelists.org/webpage/oracle-l Received on Sat Nov 22 2008 - 00:23:16 CST