Re: Triggers and table usage statistics

Auditing is what you want to do.

Look in the admin guide for how to enable.

Basically you will be setting audit_trail=true in you init.ora and then issueing a bunch of audit commands to enable auditing selects/inserts/updates/etc in the database.

btw: no triggers on selects, you would never be able to gather this information.

On Fri, 02 Aug 96 16:44:22 GMT, vhills_at_utdallas.edu (Randy Hills) wrote:

>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.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Aug 03 1996 - 00:00:00 CEST