Re: Oracle locks

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E5BDA7F.EDC35DDF_at_exesolutions.com>...
> Stefano wrote:
>
> > Hi,
> >
> > I need to keep track of when a lock is created and destroyed on a
> > specific table, I know that V$LOCK (and other views) contains the
> > information I need, but I need to create a sort of audit trail. I
> > tried with the following:
> >
> > audit insert on V$LOCK
> > audit delete on V$LOCK
> >
> > and I verified that when a lock is created one or more rows are
> > created (and can be seen) in V$LOCK, but nevertheless the addition of
> > new rows is not recorder in the audit trail table and infact:
> >
> > select USERNAME, TERMINAL,
> > to_char(TIMESTAMP,'dd-mon-yyyy hh:mm:ss'),
> > OBJ_NAME, ACTION_NAME
> > from dba_audit_object
> >
> > returns no new rows.
> > I guess that this is because rows are added somewhere else and since
> > V$LOCK is a view the events of INSERT and DELETE are not related to
> > the view itself but to the tables underneath.
> > I tried to retrieve the information about which tables lay under
> > V$LOCK view but I wasn't able.
> > Is there somebody that is willing to provide me this piece of
> > information?
> > Is there somebody that knows a different solution to my problem
> >
> > Thank you very much in advance
> >
> > Regards
> >
> > Stefano
>
> Why do you want to audit locking? Trying to see if you can slow Oracle
> down to a pathetic crawl, an academic exercise, or is there a business
> case?
>
> Also, how many transactions per time period and how many simultaneous
> users?
>
> Daniel Morgan

The business case is that I have to keep track of how much time is spent by a user on a specific record, by either editing or reviewing it. The tricky part is that the Client-Server application accessing the data is "off the shelf" and I can't modify the source code. To complicate even more they would like to keep track also of the time the users spend by simply viewing the record; they can infact open a case through the interface, review and close it withouth making any modification (but this time should be measured anyway). Being not able to see what's inside the client program, I discovered that at the Oracle level when a record is displayed on screen, the program locks it for update and therefore I thought that if I can keep track of when the lock is created and destroyed I can have the start and the end of the activity made on it, regardless of the action (insert, update or simply review).

Any suggestion?

Regards

Stefano Received on Wed Feb 26 2003 - 10:20:32 CST