Step from pid to sqltext of active session on single instance database Oracle
If you have the process ID (PID) you can back to sqltext active session associated on database using the
following steps:
1) Run the statement by inserting PID
alter session set nls_date_format = ‘dd-mm-yyyy hh24:mi:ss’;
col machine for a20
col username for a15
col OSUSER for a15
col spid for a8
col program for a35
col username for a20
col status for a9
col sid for 9999
col serial# for 99999
col sql_id FORMAT A15
col LOGON_TIME for a19
col SQL_TEXT for a60
col event for a33
col SQL_HASH_VALUE for 99999999999
set lines 220
set pages 1000
select a.sid, a.serial#, b.spid, a.PROGRAM, a.status, a.sql_id, a.logon_time
from v$session a, v$process b, v$session_wait w where b.addr=a.paddr and w.sid=a.sid
and b.spid = XXX
order by logon_time;
2) The query result will be this:
SID SERIAL# SPID OSUSER USERNAME STATUS SQL_ID LOGON_TIME
—– ——- ——– ————— ——————– ——— ————— ——————-
111 000 XXX pippo pippo ACTIVE xxxxxxxxxxxxxx 11-12-2014 09:37:51
3) Now insert the value of SQL_ID in the following statement and you have the session’s SQL_TEXT:7
SELECT
a.USERNAME, a.serial#, a.sid, a.STATUS, b.sql_text, b.SQL_ID
FROM V$SESSION a INNER JOIN V$SQLAREA b
ON a.SQL_ADDRESS= b.ADDRESS where B.SQL_ID = ‘XXXXXXXXXXXX’