Re: Blocker did not identified

Eriovaldo,

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,
Andrew

On 31 December 2017 at 12:47, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> Thansk Gogala for the answer.
>
> I think this link : https://github.com/dombrooks/Oracle-Monitoring-
> Scripts/blob/master/transactions.sql sent by Dominic can help on the
> next time that it occurs.
>
>
> Thanks
> Eriovaldo
>
>
> 2017-12-31 9:49 GMT-02:00 Mladen Gogala <gogala.mladen_at_gmail.com>:
>
>> 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 ..
>>
>> Regards
>> Eriovaldo
>>
>>
>> 2017-12-30 9:22 GMT-02:00 Dominic Brooks <dombrooks_at_hotmail.com>:
>>
>>> 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 <niall.litchfield_at_gmail.com>
>>> 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. https://docs.oracle.com/database/121/REFRN/GUID-2
>>> 8E2DC75-E157-4C0A-94AB-117C205789B9.htm#REFRN30223
>>> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fdatabase%2F121%2FREFRN%2FGUID-28E2DC75-E157-4C0A-94AB-117C205789B9.htm%23REFRN30223&data=02%7C01%7C%7Ce5abce7a2d1649633ac408d54f74d750%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636502285600939613&sdata=YN6Yr5ej5RLhXTthDuzXIHv%2BTq2KGXX%2FFTJsFT93Rjc%3D&reserved=0>
>>> IIRC EM12c also is not RAC aware in its blocking sessions page.
>>>
>>> On Sat, Dec 30, 2017 at 3:07 AM, Eriovaldo Andrietta <
>>> ecandrietta_at_gmail.com> 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:
>>>>
>>>> SELECT ID FROM TABLE_BLA
>>>> 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.
>>>>
>>>> Regards
>>>> Eriovaldo
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Niall Litchfield
>>> Oracle DBA
>>> http://www.orawin.info
>>> <https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.orawin.info&data=02%7C01%7C%7Ce5abce7a2d1649633ac408d54f74d750%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636502285600939613&sdata=mftmtNck9%2B16o4DQ2s0i3IDbgJgmSbOyXrzJtazs5B4%3D&reserved=0>
>>>
>>>
>>
>> --
>> Mladen Gogala
>> Oracle DBAhttp://mgogala.freehostia.com
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l