Triggers and table usage statistics

I am in a situation where I need to find out how much traffic is occuring for each table. I have inherited an Oracle Database that has some tables that no developer knows about; some of these tables are probably never accessed. Also I need to get a feel of which tables are accessed the most.

The only way I can think to do this are triggers. My background is Informix DBA and I am familar with the syntax for triggers in Informix but not Oracle.

What I plan to do is to create a new table with the following three columns:

unique key ...............
table_name access_type access_count

table_1        select            22
table_1        update            10
table_2        delete             3
table_2        insert            10
table_2        update            44         and so on...

After this table is created I would create triggers that would update this table. I'd create a trigger for table_1 that would update the access_count every time there is a select, I'd create a trigger for table_1 that would update the access_count every time there is a delete, etc.

Does this sound like a good way to do this? From what I see Oracle has no means to provide these statistics itself. It seems like this approach of using triggers would work. Does it seem like a good idea? Does anyone have a few create trigger statements they could send me that I could use as a model?

Thanks,

Randy Hills. Received on Fri Aug 02 1996 - 00:00:00 CEST