mapping an operating system process back to a database process

ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

SELECT

'USERNAME : ' || s.username || CHR(10) ||

'SCHEMA : ' || s.schemaname || CHR(10) ||

'OSUSER : ' || s.osuser || CHR(10) ||

'PROGRAM : ' || s.program || CHR(10) ||

'SPID : ' || p.spid || CHR(10) ||

'SID : ' || s.sid || CHR(10) ||

'SERIAL# : ' || s.serial# || CHR(10) ||

'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||

'MACHINE : ' || s.machine || CHR(10) ||

'TYPE : ' || s.type || CHR(10) ||

'TERMINAL : ' || s.terminal || CHR(10) ||

'SQL ID : ' || q.sql_id || CHR(10) ||

'SQL TEXT : ' || q.sql_text

FROM v$session s

,v$process p

,v$sql q

WHERE s.paddr = p.addr

AND p.spid = '&PID_FROM_OS'

AND s.sql_id = q.sql_id(+);

In this example, since the process is running a SQL statement, further details about the query can be extracted by generating an execution plan:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

In case you need to KILL the process:

alter system kill session '1177,38583';