Triggers and table usage statistics
Date: 1996/08/02
Message-ID: <4tte8b$mlf_at_chaos.wg.com>#1/1
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