Re: Tracking table access

dallaire_at_megatoon.com (Guy Dallaire) wrote:

>marksc_at_wpmail.code3.com (Mark A. Scarton) wrote:
 

>>As a utility appendage to the transformer, I need to be able to clean up the
>>tablespaces as tables become dormant. I need to be able to keep track of when a
>>table was last accessed (much like the Unix file system tracks access time). If
>>a table of type <foo> hasn't been accessed in the last <bar> days, I can then
>>drop the table.
 

>If the data dictionnary doesn't already have an 'last accessed date'
>column somewhere, I would suggest you use a database trigger on each
>table that gets 'touched' (read or write) that writes a timestamp in
>another table (call it the ACCESS table) in the form:
 

>TABLE_NAME LAST_ACCESS
>---------------------- -------------------------
>TEST_TABLE 02-FEB-96
>... ...
>OTHER_TABLE 23-FEB-96
 
>All you have to do after that is write a PRO*C program that scan the
>ACCESS table and drops the least used tables....
 

> Hope this helps

Triggers do not fire on a 'SELECT'. This won't necessarily tell you when the table was accessed. It _will_ tell you when it was last inserted to, deleted from, or updated.

Jared Still, Oracle DBA
RxNet, Division of Value Health
"All opinions are mine, not my employers" jared_at_valleynet.com Received on Thu Feb 29 1996 - 00:00:00 CET