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

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é
:) Received on Wed Jun 30 2004 - 04:05:11 CDT