Home » SQL & PL/SQL » SQL & PL/SQL » Identify who is locks my entity
Identify who is locks my entity [message #212278] Thu, 04 January 2007 08:33 Go to next message
clxt1
Messages: 10
Registered: January 2007
Junior Member

Ciao everybody,
is the first that I write in this forum and I want to say hello to everybody! Very Happy

As first post I have a question for you ....


I need to create a program that show me who is locking a data,
I'll give an example :


1) the user XX1 locks a data

SQL> select * from dario where pippo1='1' for update nowait;

P
-
1

2) Now another user XX2 tries to do the same

select * from dario where pippo1='1' for update nowait;

Correctly .... I have an error :

ORA-00054: resource busy and acquire with NOWAIT specified



My idea is to manage the excpetion and after show a message like :

"lock is not possible ... user XX1 is working on it" (or something like that"..


I hope to explained well my problem ... otherwise no problem, contact me!

Thanks in advance for the cooperation!


PS. Happpy new year at all !!


Dario
User : clxt1
Re: Identify who is locks my entity [message #212280 is a reply to message #212278] Thu, 04 January 2007 08:47 Go to previous messageGo to next message
clxt1
Messages: 10
Registered: January 2007
Junior Member

I have read again my post ... I missed to esplicit the question!


My focus is to write a sql (or pl/sql) in order to identify who is working on my data.


I hope that now it's better.

Thanks again in advance.

Dario
Re: Identify who is locks my entity [message #212303 is a reply to message #212280] Thu, 04 January 2007 11:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ciao Dario,

Welcome to OraFAQ.
I am afraid there is no possibility to be absolutely sure who holds the lock on the specific row you try to lock.
Oracle does not keep a list of who locks which rows; you can however see in v$locked_object which objects are locked. It COULD be that your object (eg table) is only locked by one other user, then you'd know that user blocks you. If however more than one user holds a lock on that particular table, you cannot tell which is your blocker.
icon14.gif  Re: Identify who is locks my entity [message #212308 is a reply to message #212278] Thu, 04 January 2007 11:14 Go to previous message
clxt1
Messages: 10
Registered: January 2007
Junior Member

Thanks for your quicly replies!


I'll follow your advice!


Thanks again !

clxt1
Previous Topic: Execute SQL Saved in Table
Next Topic: Can Index Size is bigger than table Size
Goto Forum:
  


Current Time: Mon Dec 05 14:59:08 CST 2016

Total time taken to generate the page: 0.07774 seconds