FAQ
Dear All,

I would like to simplify the infrastructure by letting the Oracl RAC take
care of system failure at the level of the system, do not need to provide
extra network cards, and bonding for redundancy of the private, and public
Ethernet interconnects? why should we have also two SAN switches. what do
you think?

regards

Walid

Search Discussions

  • SHEEHAN, JEREMY at Nov 19, 2008 at 2:19 pm
    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
  • Jan-Hendrik.Boll_at_dataport.de at Nov 19, 2008 at 2:25 pm
    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
    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
  • SHEEHAN, JEREMY at Nov 19, 2008 at 2:28 pm
    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 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 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
  • Powell, Mark D at Nov 19, 2008 at 2:44 pm
    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
    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
    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
    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
  • Daniel Fink at Nov 19, 2008 at 2:37 pm
    If you are using statspack or licensed for AWR, you can look at the
    historical data. You need to look for the table name in sql statements
    and any views that reference that table.

    Statspack - stats$sqltext contains the actual text of a statement. Using
    the sql_ids from this table, you can query stats$sql_summary and
    stats$snapshot to get the time information
    AWR - dba_hist_sqltext contains the actual text of a statement. Use the
    sql_id to access dba_hist_sqlstat and dba_hist_snapshot

    Regards,
    Daniel Fink

    --
    Daniel Fink

    OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

    OptimalDBA http://www.optimaldba.com
    Oracle Blog http://optimaldba.blogspot.com

    Lost Data? http://www.ora600.be/

    SHEEHAN, JEREMY wrote:
    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
  • SHEEHAN, JEREMY at Nov 19, 2008 at 2:43 pm
    I believe we're using statspack there. This is a 9i DB so we can't use AWR on it, but I think we're slated to upgrade soon so that's another possibility!

    Thanks!

    Jeremy

    From: oracle-l-bounce_at_freelists.org On Behalf Of Daniel Fink
    Sent: Wednesday, November 19, 2008 9:37 AM
    To: SHEEHAN, JEREMY
    Cc: oracle-l
    Subject: Re: Table last access date?

    If you are using statspack or licensed for AWR, you can look at the historical data. You need to look for the table name in sql statements and any views that reference that table.

    Statspack - stats$sqltext contains the actual text of a statement. Using the sql_ids from this table, you can query stats$sql_summary and stats$snapshot to get the time information
    AWR - dba_hist_sqltext contains the actual text of a statement. Use the sql_id to access dba_hist_sqlstat and dba_hist_snapshot

    Regards,
    Daniel Fink

    --

    Daniel Fink

    OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

    OptimalDBA http://www.optimaldba.com

    Oracle Blog http://optimaldba.blogspot.com

    Lost Data? http://www.ora600.be/

    SHEEHAN, JEREMY wrote:
    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
  • Michael Elkin at Nov 19, 2008 at 9:43 pm
    You can fetch the last time some table's block has been updated by fetching:
    select max(ora_rowscn) from my_tab;

    You can always convert scn to timestamp

    select max(scn_to_timestamp(ora_rowscn)) from my_tab;

    Michael
    On Wed, Nov 19, 2008 at 4:19 PM, SHEEHAN, JEREMY wrote:

    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
    --
    Best Regards
    Michael Elkin

    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Fontana at Nov 19, 2008 at 9:52 pm
    <>

    While that's a neat trick to identify an update, I believe the goal was
    to identify "access", because the requestor was looking for unused tables
    to drop.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 19, '08 at 1:46p
activeNov 19, '08 at 9:52p
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase