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
<andysayer@xxxxxxxxx<mailto:andysayer@xxxxxxxxx>> wrote:
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@xxxxxxxxx<mailto:ecandrietta@xxxxxxxxx>> wrote:
Thansk Gogala for the answer.
I think this link :
https://github.com/dombrooks/Oracle-Monitoring-Scripts/blob/master/transactions.sql<https://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdombrooks%2FOracle-Monitoring-Scripts%2Fblob%2Fmaster%2Ftransactions.sql&data=02%7C01%7C%7Cef0bbc07b8674e249efa08d55057281c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636503257619226967&sdata=7ZpL853kKA5usjYBcKnCvugbZ5NpWmQb74q8dffp6RA%3D&reserved=0>
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<mailto: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<mailto: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<mailto: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<mailto: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 DBA
http://mgogala.freehostia.com<https://nam03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmgogala.freehostia.com&data=02%7C01%7C%7Cef0bbc07b8674e249efa08d55057281c%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636503257619226967&sdata=F5f8XLjWg0d26dKWULZtZ685iatpeEiMg3A1sx2RH%2B4%3D&reserved=0>
