RE: Table last access date?
Date: Thu, 20 Nov 2008 08:15:47 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360582CBF9@CWYMSX04.Corp.Acxiom.net>
Jeremy,
In following this thread, something that has stuck out to me is whether or not you need this from existing data or if you'll need this for future need. Assuming the former:
- You could check V$SQL, but the any statement(s) accessing the table could have already been flushed from the shared pool. Access statements could also include DDL, which won't be in the shared pool as of 9i. And, you may have a statement accessing the table that has an exceeding long column list, so the table name may get truncated. V$SQLTEXT doesn't have the truncation problem, but you'll have to append the 80 char lines together as the table name may get split across multiple lines.
- If you've got monitoring enabled for your tables, you could check DBA_TAB_MODIFICATIONS for the latest DML access. Again, only works if you have monitoring enabled and that information gets reset the next time the table is analyzed. So you could use info from this view along with DBA_TABLES.LAST_ANALYZED, but as I said that's only for DML access.
- As some have suggested, you could use info from STATSPACK, but that is dependent on the access statement causing STATSPACK thresholds to be surpassed, otherwise the statement won't get "snapped" by STATSPACK. Also, if the table in question has a parallel degree > 1, there's a chance that even if an access statement surpassed STATSPACK set thresholds, the statement might be from one of the PX slaves, in which case you may end up with "SELECT C0 C0, C1 C1, ... FROM :Q12345 ..."
I'm not trying to be too negative here on what you can expect, but instead setting your expectations. For historical data on access, you'll have to use a combination of a number of methods and even with that it'll be a best guess.
HTH Dave
Dave Herring, DBA | A c x i o m M I C S / C S O 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Freeman Sent: Wednesday, November 19, 2008 6:00 PM To: Oracle-L Freelists
Subject: Fw: Table last access date?
Version??? If you are using 9i and later look at fine grained auditing. I believe this will give you just what you are looking for.
RF
Robert G. Freeman
From: "SHEEHAN, JEREMY" <JEREMY.SHEEHAN_at_fpl.com> To: oracle-l <oracle-l_at_freelists.org>
Sent: Wednesday, November 19, 2008 7:19:37 AM Subject: 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
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank You.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 20 2008 - 08:15:47 CST