FAQ
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
Author:
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)

From: "SHEEHAN, JEREMY"
To: oracle-l
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

Search Discussions

  • Herring Dave - dherri at Nov 20, 2008 at 2:15 pm
    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 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"
    To: oracle-l
    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.
  • Yong Huang at Nov 20, 2008 at 6:06 pm

    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.
    If you decide to use that column, why not use last_active_time? It doesn't require the cursor to be reloaded so it may well be more recent. But of course, this column only exists in 10g and up.

    In case the table name doesn't exist in v$sql% (because the SQL uses synonyms, packages/functions, etc.), you can check v$bh or x$bh for blocks that belong to the table in question. Limitations are obvious though.

    Yong Huang
  • John Kanagaraj at Nov 22, 2008 at 6:23 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 20, '08 at 12:00a
activeNov 22, '08 at 6:23a
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase