Re: Triggers and table usage statistics

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.

You can use Oracle's audit features instead. You can enable auditing for the specific tables you are concerned with. Make sure you audit at the session level or you will get more data than you can deal with.

Dave Roth Received on Fri Aug 02 1996 - 00:00:00 CEST