Re: How to determine details of a LOCK as a non-SYSDBA user

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<40e3df1b_at_olaf.komtel.net>...
> "Joel Garry" <joel-garry_at_home.com> schrieb im Newsbeitrag
> news:91884734.0406301352.b9e863_at_posting.google.com...
> > "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message
> news:<40e28248$1_at_olaf.komtel.net>...
> > > Hi everyone,
> > >
> > > I am interested to know how in an Oracle9i or higher environment an
> Oracle
> > > user that is not a DBA (i.e. not SYS or SYSTEM) can determine the
> details of
> > > a lock that occurred on a table of that same user's schema.
> > >
> > > The background is the following: I have an application that is
> installed
> > > on many client computers, accessing an Oracle instance using only one
> Oracle
> > > account. If a statement fails because of a lock (for example ORA-00054:
> > > resource busy and acquire with NOWAIT specified) I want to display to
> the
> > > end user on which table the lock occurred, which current sessions to the
> > > server hold a lock on that table and what are the OS users and computer
> > > names/IP numbers behind the sessions... hence allowing the user to
> > > coordinate work with the other user that is obviously working on the
> same
> > > data right now. There could be a dialog like "The following users
> (logged in
> > > from the computers shown) currently have locks on the data you want to
> > > modify: user1 from computer1, ...". That desire is there because if I
> just
> > > report to the user the is a lock preventing the current action and "try
> > > again later" he or she is left bare-handed and this is not
> satisfactorily.
> > >
> > > I know that all the information that I want is available somehow, like
> in
> > > OEM's overview of current locks in the database. I am also aware of the
> > > V_$LOCK and other views but they are only accessible for SYS/SYSTEM. I
> didnt
> > > find anything suitable among the ALL_ and USER_ views. Thats why I am
> > > posting here to see if there is an alternative way instead of asking the
> DBA
> > > there to grand SELECT on the system views involved. I know that our
> customer
> > > handles configurations and grants like this VERY restrictively and I
> would
> > > try to work around that as a first choice.
> > >
> > > André
> > > :)
> >
> > When I've been faced with this sort of thing in the past I've been
> > lucky enough to be on unix servers, where you can do things like use
> > sudo to allow the user to run only designated scripts that they can't
> > see the passwords for with higher user privileges. Then I could not
> > only look at things like those views, but could get information out of
> > ps or the listener log too, or even kill things.
> >
> > If you want to keep it pure oracle, investigate how stored procedures
> > get privileges.
> >
> > Remember to post platform and exact version information.
>
> We are talking about Oracle9i here. Our test environment runs the server
> under Win2000 SP2, our customer runs it on HPUX. However our software is
> supposed to operate independently upon the Oracle Server's operating system,
> it must even run under all Oracle Versions 8i ... 10g. So, yes, I would
> prefer a solution that remains withing the Oracle scope and does not invoke
> any OS-dependant functionality. The customer consider their server farm as
> holy ground... meaning we have very little influence on server side
> configurations (like granting roles/rights, organizing tablespaces and SGA
> and so on). In addition to that our application will be only one out of
> dozens using a particular server, meaning that we cant tune the instance to
> the needs of our aplication because that might tune it contrary to the needs
> of another application. Of course I know that separation and individual
> tailor-making would be the best but as we say in german "Der Kunde ist
> König"... the customer is the king and if their Ora service department is
> unwilling to cooperate we have to struggle with the end user department (of
> our application) who complains about this and that :)
>
> >
> > jg
> > --
> > @home.com is bogus.
> > "The white woman is Kate Smith... and the black woman is Lena Horne."
> > - Lenny Bruce.
> > Happy 87th birthday, Lena Horne.

Andre, as sys you can grant select privileges on the underlying v_$ views to a user or role to allow select privileges on selected v$ views. The the users can run scripts against these views. Or you can provide a screen as part of your application that provides this feature. This might be the best solution since you would not have to worry about the SQL being used by the users since you provided it. Storing the code in packages is certainly an option.

But with each Oracle upgrade these privileges will need to be re-issued so provide the DBA or local Administrator instructions or a tool to re-establish the grants.

HTH -- Mark D Powell -- Received on Thu Jul 01 2004 - 09:07:26 CDT