Find sql being executed by OS Process ID
Find top pids of oracle user (OS level)
top -u oracle
Enter the os pid and find the sql id
select sid, sql_id from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &OS_PID;
Enter the sql_id to find the sql_text
select sql_text
from v$sql
where sql_id = ‘&sql_id’;
Finding sql being executed by OS Process ID
select s.username su, substr(sa.sql_text,1,540) tex
from v$process p, v$session s,v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
You can use the following query to find out which query is working on this process and what the session information is.
Find top pids of oracle user (OS level)
top -u oracle
select t.sql_id,t.sql_text from gv$process p , gv$session s , gv$sqlarea t
where p.addr=s.paddr
and s.sql_address=t.address
and s.sql_hash_value=t.hash_value
and p.spid = 2424;
