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