Stefano wrote:
> 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).
>
If, as I understand it, this OTB application does a SELECT <something>
FOR UPDATE each time the user queries a record then I would get out of
that shop ASAP because you will be in for NOTHING BUT GRIEF. A well
written application will do a select to present the data then another
select for update immediately before applying the changes -- to confirm
that the record has not changed while it is being viewed/edited.
Individual row updates will take miliseconds so measuring this is moot.
As for measuring how long a user is viewing/editing a record, cannot be
done -- at least not with any degree of reliability and accuracy.
--
AjA
Received on Wed Feb 26 2003 - 15:22:14 CST
