關於RAC環境下鎖查詢的測試案例1-2

set linesize 120

set pagesize 66

col c0 for 999

col c0 heading "INS"

col c1 for a9

col c1 heading "OS User"

col c2 for a9

col c2 heading "Oracle User"

col c3 for a15

col c3 heading "Program Name"

col b1 for a9

col b1 heading "Unix PID"

col b2 for 9999 justify left

col b2 heading "ORA SID"

col b3 for 999999 justify left

col b3 heading "SERIAL#"

col sql_text for a45

set space 1

break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2

select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text

from gv$sql a, gv$session b, gv$process c

where

a.address = b.sql_address

and b.paddr = c.addr

and a.hash_value = b.sql_hash_value

and a.inst_id=b.inst_id and a.inst_id=c.inst_id

and a.inst_id like '&inst_id' and b.sid like '&sid'

order by c.spid,a.hash_value

/

查詢第一個節點上的業務可能查不到，因為第一個節點上應用處於空閒狀態，可以檢視到第二個節點上的業務應用

Enter value for inst_id: 1

Enter value for sid: 2148

old 8: and a.inst_id like '&inst_id' and b.sid like '&sid'

new 8: and a.inst_id like '1' and b.sid like '2148'

no rows selected

SQL> /

Enter value for inst_id: 2

Enter value for sid: 1051

old 8: and a.inst_id like '&inst_id' and b.sid like '&sid'

new 8: and a.inst_id like '2' and b.sid like '1051'

INS ORA SID Unix PID Program Name Oracle Us SERIAL# SQL_TEXT

---- ------- --------- --------------- --------- ------- ---------------------------------------------

2 1051 25066 sqlplus@SERV-TEST SYS 31100 update system.akdas set a1=11 where a1=6