Re: Blocker did not identified

From: Dominic Brooks <>
Date: Sun, 31 Dec 2017 15:38:30 +0000
Message-ID: <HE1P190MB0250434240927A3CC509DABCA11B0_at_HE1P190MB0250.EURP190.PROD.OUTLOOK.COM>

Have had quite a few different scenarios. But struggling to remember anything other than those involving XA transactions where something has happened between the two phases of commit and there is no longer a session attached to the transaction. A transaction doesn’t need a session and with XA transactions, sessions can actually attach/detach to the transaction programmatically.

Sent from my iPhone

On 31 Dec 2017, at 14:02, Andy Sayer <<>> wrote:


What was the actual result of the query you originally posted? What was the value for the blocking_session? I can't see any reason, from what you've shared, that the problem was exotic enough to require anything other than this value (it's the SID of the blocker)

"I used a lot of queries that show blocker and locked but without sucess to see the blocker."

Like what? Either something was wrong with the queries or your interpretation of them was wrong. If you share them, the results and your interpretation then I'm sure someone can explain the problem.

Anecdotally, I've never had a session waiting on a lock where the blocking session wasn't reported by v$session.blocking_session - of course, sometimes there's a chain of blockers but it's never been too difficult to follow the chain.

Regards and happy new year,

On 31 December 2017 at 12:47, Eriovaldo Andrietta <<>> wrote: Thansk Gogala for the answer.

I think this link :<> sent by Dominic can help on the next time that it occurs.


2017-12-31 9:49 GMT-02:00 Mladen Gogala <<>>: All locks are identified in V$LOCK table. You may be looking for an event which functions as a lock, like waiting for a checkpoint to complete, but there aren't any locks which are not identified in V$LOCK. Regards

On 12/30/2017 05:32 PM, Eriovaldo Andrietta wrote: Tks Dominic,

This is exactly I am looking for:
What is the way to check lock that is not identified by v$session and v$lock and v$transactions ..


2017-12-30 9:22 GMT-02:00 Dominic Brooks <<>>: Also... technically it’s transactions not sessions which hold locks. In some niche circumstances, that can make a difference in how to identify.

Sent from my iPhone

On 30 Dec 2017, at 11:02, Niall Litchfield <<>> wrote:

Is this a RAC database? You'll need to look also at the instance and blocking_status columns in v$session to rely on the blocking_session column in v$session.<> IIRC EM12c also is not RAC aware in its blocking sessions page.

On Sat, Dec 30, 2017 at 3:07 AM, Eriovaldo Andrietta <<>> wrote: ​​​Hello,

I got an issue related to lock.
Now the daabase was re-started and the issue is solved. But during investigation I did not get sucess to identify who were locking a table.

A table is used like this:

where id = :1
for upate;

The query did not return the result and looking for lock using this query :

 select s.sid, s.blocking_session, do.object_name,

    row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,     dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )     from v$session s, dba_objects do
    where s.ROW_WAIT_OBJ# = do.OBJECT_ID

I saw the row_wait_obj#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# values.

In this situation how can I find the blocker? or
I used a lot of queries that show blocker and locked but without sucess to see the blocker.



Niall Litchfield
Oracle DBA<>


Mladen Gogala
Oracle DBA<>

-- Received on Sun Dec 31 2017 - 16:38:30 CET

Original text of this message