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

"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.

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.