How to determine if a table has been accessed in the last month?

Question

In Oracle 10, is it possible to determine when the last time a table was accessed?

According to this article the data is there, but I'm not sure whether this is reset whenever the server is restarted, nor how to actually get the access information.

We're actually trying to determine what tables are no longer used.

Answer 1

Hi,

Have you had a look at this asktom question http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621 ?

It takes a while to get going but if you search for "simple way last date of table used" it may be of some use.

Hope it helps

Cheers

Ian

Answer 2

The best way to determine table modifications is to check DBA_TAB_MODIFICATIONS, their values remain throught shutdown. You have to do a comparision between two intervals of time.

UPDATE: About the article. It refers to DBA_HIST_SEG_xxx tables which gets data from V$segstat. The data in this system view is reset on startup.