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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/48010/viewspace-1015840/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/48010/viewspace-1015840/