Unix Pid to Oracle  Sql ..

Unix Command 

top -U oracle -d 5 -n 25
iotop
iotop -user oracle
alias topmem='ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head'

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
RPAD('USERNAME : ' || s.username,80) ||
RPAD('OSUSER   : ' || s.osuser,  80) ||
RPAD('PROGRAM  : ' || s.program, 80) ||
RPAD('SPID     : ' || p.spid,    80) ||
RPAD('SID      : ' || s.sid,     80) ||
RPAD('SERIAL#  : ' || s.serial#, 80) ||
RPAD('MACHINE  : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal,80) ||
RPAD('SQL TEXT : ' || q.sql_text,80)
FROM v$session s
,v$process     p
,v$sql         q
WHERE s.paddr        = p.addr
AND p.spid           = '&PID_FROM_OS'
AND s.sql_address    = q.address(+)
AND s.sql_hash_value = q.hash_value(+);
  

@pid
Enter value for pid_from_os: 10165
old  15: AND p.spid = '&PID_FROM_OS'
new  15: AND p.spid = '10165'

USERNAME :
OSUSER   : oracle
PROGRAM  : oracle@apt-amd-02 (VKTM)
SPID     : 10165
SID      : 3
SERIAL#  : 1
MACHINE  : apt-amd-02
TERMINAL : UNKNOWN
SQL TEXT :

*****************************************

SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT 'dummy1' dummy_value,
'USERNAME   : ' || s.username       || CHR(10) ||
'SCHEMA     : ' || s.schemaname     || CHR(10) ||
'OSUSER     : ' || s.osuser        || CHR(10) ||
'MODULE     : ' || s.program        || CHR(10) ||
'ACTION     : ' || s.schemaname       || CHR(10) ||
'CLIENT INFO   : ' || s.osuser        || CHR(10) ||
'PROGRAM   : ' || s.program        || CHR(10) ||
'MACHINE   : ' || s.machine        || CHR(10) ||
'TYPE    : ' || s.type         || CHR(10) ||
'TERMINAL   : ' || s.terminal        || CHR(10) ||
'CPU     : ' || q.cpu_time/1000000      || CHR(10) ||
'ELAPSED_TIME  : ' || q.elapsed_time/1000000   || CHR(10) ||
'BUFFER_GETS   : ' || q.buffer_gets       || CHR(10) ||
'SQL_ID   : ' || q.sql_id        || CHR(10) ||
'CHILD_NUM   : ' || q.child_number       || CHR(10) ||
'STATUS   : ' || s.status            || 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) ||
'START_TIME   : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi')  || CHR(10) ||
'SQL_TEXT   : ' || q.sql_fulltext
FROM v$session s
JOIN v$process p ON (s.paddr = p.addr)
LEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)
WHERE 1=1 
AND s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,'x') NOT LIKE '%dummy1%' -- eliminates this query from output
AND p.spid = '&PID_FROM_OS'
ORDER BY q.cpu_time;