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@xxxxxxxxx>:
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@xxxxxxxxxxx>:
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@xxxxxxxxx>
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@xxxxxxxxx> 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