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

"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.
Received on Thu Jul 01 2004 - 04:53:29 CDT