Re: Oracle locks

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E5CED0E.594C0F4_at_exesolutions.com>...
> 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

This sounds like a very poorly written app, from the Oracle point of view, where someone did not understand Oracle's locking and integrity mechanisms.

> > 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
>
> You can not do what has been asked without modifying the front-end source code. And I don't even want
> to get into the complications caused by two people viewing the same record or a multi-record request in
> a master-detail relationship. Sounds to me like someone in management has way too much time on their
> hands. Tell them it can't be done per my first sentence and hopefully get assigned to doing something
> useful. If not ... polish up your resume' and move on.

Daniel is spot on here. Tell management it would be cheaper to put little cameras on everyone's work area, including their own, except employee turnover costs may go up a bit.

jg

--
@home is bogus.
Nothing more to see here, move along, move along.