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?

mustaccio
  • 25,896
  • 22
  • 57
  • 72
rado
  • 119
  • 1
  • 6

1 Answers1

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

mustaccio
  • 25,896
  • 22
  • 57
  • 72
  • 3
    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 '19 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 '19 at 07:40