Re: Blocker did not identified

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 31 Dec 2017 06:49:54 -0500
Message-ID: <f345655b-2ad8-6dc7-9587-4dfc83332ddf_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
> <mailto: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 <mailto: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-28E2DC75-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 <mailto: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 DBA
http://mgogala.freehostia.com


--
http://www.freelists.org/webpage/oracle-l
Received on Sun Dec 31 2017 - 12:49:54 CET

Original text of this message