1

I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the query from the linked question, I was able to discover 'someTable'. I want to retrieve the full query. Is is possible?

0

To see each session's currently running SQL (which supposedly holds the lock) you can join v$session with v$sqltext. I don't have anywhere to test this but something along these lines (using the answer you linked to as a starting point):

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine,
   s.sql_text
from
   v$locked_object a ,
   v$session b,
   dba_objects c,
   v$sqltext s
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and s.address = b.sql_address and s.hash_value = b.sql_hash_value 
and s.piece = 1
;

This will retrieve the first "piece" of the SQL statement. If you need the complete statement, see for example this link on "Ask Tom".

  • 2
    You can see what session is holding the blocking lock, but not necessarily the actual sql statement. The problem is that v$session only points to the most recent statement processed by that session. It could very well be that the offending session issues multiple sql statements without a commit, and the statement that created the lock in question was not the most recent of those several statements. – EdStevens Jan 11 at 18:14
  • Agree with Ed. You can look at V$ACTIVE_SESSION_HISTORY but even that can miss the offending statement. The only way to find it is in a trace of the session, and even then it might be hard to find the SQL that caused the lock. The good news is that it's usually obvious as a select for update runs for long enough to catch it. – sandman Jan 14 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.