How to get SID, oracle query using Unix/Linux process id?
First:
In OS prompt type top command .
[oracle@TTd-monitor01 ~]$ top
You need to get PID(process id) from top/glance output as below
now i will be considering the PID(processid) 5726 and would like to see the query , executed USER ,SQL_HASH_VALUE .
SQL> set lines 120
select a.sid,b.pid,a.program cprogram,a.process,b.program sprogram,b.spid from v$session a,v$process b
where a.paddr(+)=b.addr and b.spid=&1order by 1
/
i gave PID as input in the above query and got SID
Second:
Now try to find client program/client machine, username,sql_hash_value etc
set echo off
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser, substr(program||module,1,15) program, substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,sql_hash_value, last_call_et "last call",statusfrom v$session
where sid=&1 order by 1
/
now i will give SID= 92 got from above query.
now you got the USER, status of session, client machine etc. You can get the sql query using below query.
Third:
select sql_text from v$sqltext where hash_value=&1 order by piece
/
this will give the query that is running with PID 5726.
Cheers...!!!!!!!
First:
In OS prompt type top command .
[oracle@TTd-monitor01 ~]$ top
You need to get PID(process id) from top/glance output as below
now i will be considering the PID(processid) 5726 and would like to see the query , executed USER ,SQL_HASH_VALUE .
SQL> set lines 120
select a.sid,b.pid,a.program cprogram,a.process,b.program sprogram,b.spid from v$session a,v$process b
where a.paddr(+)=b.addr and b.spid=&1order by 1
/
i gave PID as input in the above query and got SID
Second:
Now try to find client program/client machine, username,sql_hash_value etc
set echo off
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser, substr(program||module,1,15) program, substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,sql_hash_value, last_call_et "last call",statusfrom v$session
where sid=&1 order by 1
now i will give SID= 92 got from above query.
now you got the USER, status of session, client machine etc. You can get the sql query using below query.
Third:
select sql_text from v$sqltext where hash_value=&1 order by piece
/
this will give the query that is running with PID 5726.
Cheers...!!!!!!!
1 comment:
i am listing the same queries mentioned above with adjusted column values :
set lines 120
col cprogram form a30 trunc head "Client|Program"
col sprogram form a30 trunc head "Server|Program"
col sid form 9999
col pid form 9999
col process head "Client|Process|ID" form a10
col spid head "Oracle|Background|ProcessID" form 99999
select a.sid,b.pid,a.program cprogram,a.process,b.program sprogram,b.spid
from v$session a,v$process b
where a.paddr(+)=b.addr
/*order by to_number(b.spid)*/
and b.spid=&1
order by 1
/
set echo off
set linesize 130
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a8 trunc
col osuser form a7 trunc
col machine form a20 trunc head "Client|Machine"
col program form a15 trunc head "Client|Program"
col login form a11
col "last call" form 9999999 trunc head "Last Call|In Secs"
col status form a6 trunc
select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
substr(program||module,1,15) program,substr(machine,1,22) machine,
to_char(logon_time,'ddMon hh24:mi') login,sql_hash_value, last_call_et "last call",status
from v$session
where sid=&1
order by 1
/
select sql_text from v$sqltext where hash_value=&1
order by piece
/
Post a Comment