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?
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".
-
3You 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 7:40
