[Oracle-l] level of redundancy in an Oracle RAC extended cluster

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

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

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

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

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

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

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

<>

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.