Unix Pid to Sql

run a top command



PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
11149 oracle 1 0 0 3204M 3188M cpu/0 124:11 45.67% oracle ----<<<
11485 oracle 1 32 0 3205M 3188M cpu/0 0:49 18.47% oracle
11489 oracle 1 59 0 2352K 1864K cpu/2 0:00 0.16% top
11496 oracle 1 59 0 3204M 3187M sleep 0:00 0.09% oracle
647 oracle 15 59 0 3216M 3163M sleep 64:19 0.08% oracle
655 oracle 1 59 0 3208M 3166M sleep 60:06 0.08% oracle
847 noaccess 25 59 0 192M 54M sleep 172:23 0.07% java
4048 noaccess 25 59 0 222M 58M sleep 171:54 0.07% java
659 oracle 1 59 0 3203M 3165M sleep 54:39 0.06% oracle
639 oracle 1 59 0 3204M 3170M sleep 36:28 0.05% oracle
740 oracle 11 59 0 3207M 3176M sleep 34:36 0.05% oracle
137 root 15 59 0 8328K 1104K sleep 74:44 0.03% picld
649 oracle 21 59 0 3208M 3166M sleep 29:06 0.03% oracle
645 oracle 258 59 0 3224M 3177M sleep 35:50 0.02% oracle
744 oracle 1 59 0 3204M 3176M sleep 14:24 0.01% oracle

PID 11149 taking lot of cpu i.e. 45.67%

run this sql find out sql text, session sid and serial no


set linesize 200
col SID_serial format a15
select ses.sid||','||ses.SERIAL# SID_serial,sqa.SQL_TEXT SQL
from v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&pid_sql;

if you want to kill this process then run this sql



alter system kill session 'ses.sid,ses.SERIAL# ' ;