pid 로 수행중인 sql 찾기

root 계정에서 pid.sh, pid.sql 생성하여 실행

 

1) pid.sh

========================================================================

su - oracle -c "sqlplus -S / as sysdba << EOF

@~/pid.sql $1

exit

EOF"

========================================================================

 

 

2) pid.sql

========================================================================

select /*+ use_hash(a,b,c) */
a.username, a.program, a.machine, a.module, b.spid, a.sid, a.serial#, a.sql_hash_value,
d.PHYSICAL_READS, d.BLOCK_GETS, c.sql_text
from v$session a, v$process b, v$sql c, v$sess_io d
where b.spid = '&pid'
and b.addr = a.paddr
and a.sid = d.sid
and a.sql_hash_value = c.hash_value(+)
and a.sql_address = c.address(+);

========================================================================

 

 

3) 실행

pid.sh [process no]