Database Reference
In-Depth Information
Listing 10-1.
Gv$ges_enqueue Output
col state format a15
RS@ORCL2:2>SELECT resource_name1, grant_level, state, owner_node
FROM v$ges_enqueue
WHERE resource_name1 LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME1 GRANT_LEV STATE OWNER_NODE
------------------------------ --------- ---------- ----------
[0xb4][0x4],[BL][ext 0x0,0x0] KJUSERPR GRANTED 1
Connecting to instance ORCL1 (with inst_id=1), I will query the same row to read data blocks into instance
1 buffer cache.
RS@ORCL1:1> SELECT n1,
dbms_rowid.rowid_to_absolute_fno (rowid, 'RS','T_ONE') fno,
dbms_rowid.rowid_block_number(rowid) block,
dbms_rowid.rowid_object(rowid) obj,
LENGTH(v1) v1
FROM rs.t_one
WHERE n1=100;
N1 FNO BLOCK OBJ V1
---------- ---------- ---------- ---------- ----------
100 4 180 75742 250
Accessing gv$ges_resource, as printed in the following query output, we see that block is still mastered by
instance 1.
RS@ORCL2:2> SELECT resource_name, ON_CONVERT_Q, ON_GRANT_Q, MASTER_NODE
FROM gv$ges_resource
WHERE resource_name LIKE '[0xb4][0x4],[BL]%';
RESOURCE_NAME ON_CONVERT_Q ON_GRANT_Q MASTER_NODE
------------------------------ ------------ ---------- -----------
[0xb4][0x4],[BL][ext 0x0,0x0] 0 1 1
Accessing gv$ges_enqueue, there are two locks on that resource, which are owned by instances 1 and 2
in KJUSERPR (PR) mode. Since sessions are accessing the block in read-only mode, locks are acquired in PR
(KJUSERPR) mode. Both locks can be held in the KJUSERPR mode, as the lock mode KJUSERPR is compatible with
another KJUSERPR mode.
KJUSERPR mode protects concurrent changes to the block. So, if another session connected to instance 3 tries to
change the block, then both instances 1 and 2 must downgrade the block before instance 3 can acquire the lock on the
resource in exclusive mode.
RS@ORCL2:2>SELECT resource_name1, grant_level, state, owner_node
FROM v$ges_enqueue
WHERE resource_name1 LIKE '[0xb4][0x4],[BL]%'
/
RESOURCE_NAME1 GRANT_LEV STATE OWNER_NODE
------------------------------ --------- ---------- ----------
[0xb4][0x4],[BL][ext 0x0,0x0] KJUSERPR GRANTED 1
[0xb4][0x4],[BL][ext 0x0,0x0] KJUSERPR GRANTED 0
