Locked object in gv$locked_object without transaction in gv$transaction [message #663272] |
Sat, 27 May 2017 08:19 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I am trying to understand why a certain object ( table , object_id = 189035) can appear in gv$locked object:
Connected to:
SQL>
SQL>
SQL>
SQL> select 1 from gv$locked_object a where object_id = 189035;
1
----------
1
1
SQL>
And when I build my query using "exists" to correlate to gv$transaction I also get record:
SQL> select 1
2 from gv$locked_object a
3 where exists (select 1
4 from gv$transaction c
5 where a.xidusn = c.xidusn
6 and a.xidslot = c.xidslot)
7 and a.object_id = 189035
8 and exists
9 (select 1 from dba_objects b where b.OBJECT_ID = a.object_id)
10 ;
1
----------
1
However it *does not show* to have a corresponding record in GV$TRANSACTION when I *join* them:
SQL> select 1
2 from gv$locked_object a, gv$transaction c, dba_objects b
3 where a.xidusn = c.xidusn
4 and a.xidslot = c.xidslot
5 and a.object_id = 189035
6 and b.OBJECT_ID = a.object_id;
no rows selected
SQL>
Now , this happens only with this table.
If I try to create a copy of that table (object_id = 241566) and I do the query on it, it works for every way I try:
SQL> lock table table1 in exclusive mode;
Table(s) Locked.
SQL> select 1 from table1 for update;
1
----------
1
1
1
1
1
1
1
1
8 rows selected.
SQL>
SQL> select 1
2 from gv$locked_object a, gv$transaction c, dba_objects b
3 where a.xidusn = c.xidusn
4 and a.xidslot = c.xidslot
5 and a.object_id = 241566
6 and b.OBJECT_ID = a.object_id;
1
----------
1
SQL>
SQL>
SQL> select 1
2 from gv$locked_object a
3 where exists (select 1
4 from gv$transaction c
5 where a.xidusn = c.xidusn
6 and a.xidslot = c.xidslot)
7 and a.object_id = 241566
8 and exists
9 (select 1 from dba_objects b where b.OBJECT_ID = a.object_id);
1
----------
1
SQL>
SQL>
How can I understand what is going on here ?
why isn't my join working , and why specific for this table ?
Many thanks in advance,
Andrey R
[Updated on: Sat, 27 May 2017 08:22] Report message to a moderator
|
|
|
|
|
|
Re: Locked object in gv$locked_object without transaction in gv$transaction [message #663738 is a reply to message #663272] |
Thu, 15 June 2017 10:25 |
|
Caffeine+
Messages: 14 Registered: February 2017
|
Junior Member |
|
|
I'm unable to reproduce the issue in 11.2.0.4. Can you post the table's DDL? Here's my try at reproducing a test case:
SQL> drop table bogus
>> drop table bogus
Error at line 3
ORA-00942: table or view does not exist
SQL> create table bogus as select * from dba_objects
Table created.
SQL> select count(*) from bogus
COUNT(*)
----------
134719
1 row selected.
SQL> lock table bogus in exclusive mode
Lock complete.
SQL> select object_id,data_object_id from user_objects where object_name='BOGUS'
OBJECT_ID DATA_OBJECT_ID
---------- --------------
2849099 2849099
1 row selected.
SQL> select * from gv$locked_object where object_id = 2849099
INST_ID XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
1 0 0 0 2849099 1722 ***** ****** 10100:3044 6
1 row selected.
SQL> SELECT 1
FROM gv$locked_object a
WHERE EXISTS
(SELECT 1
FROM gv$transaction c
WHERE a.xidusn = c.xidusn AND a.xidslot = c.xidslot)
AND a.object_id = 2849099
AND EXISTS
(SELECT 1
FROM dba_objects b
WHERE b.OBJECT_ID = a.object_id)
no rows selected.
|
|
|