2023-04-10 11:08阅读: 39评论: 0推荐: 0

Check SID and SQL query associated with OS Process ID(PID) in Oracle

check Session id from OS process id in Oracle

SELECT b.spid,
a.sid,
a.serial#,
a.username,
a.osuser
FROM v$session a, v$process b
WHERE a.paddr = b.addr AND b.spid = '&spid'
ORDER BY b.spid

Check SQL statement associated with Process id in Oracle

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 s.sql_address = q.address AND s.sql_hash_value = q.hash_value
AND p.spid = '&spid'

If you have process id (SPID) with use following query

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S
WHERE S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND s.sid in (select s.sid from v$session s , v$process p
where p.addr =s.paddr
and p.spid in (&ProcessID)) ORDER BY S.SID, T.PIECE;

If you have SID with you use following query

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S
WHERE S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND s.sid in (&sid) ORDER BY S.SID, T.PIECE;

If you wanted to find out the sql text for all the long running sessions then use following query

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT s.sid, s.username, T.SQL_TEXT
FROM v$SQLTEXT T, v$SESSION S
WHERE S.SQL_ADDRESS=T.ADDRESS
and s.status ='ACTIVE'
AND s.sid in (select sid from v$session_longops where time_remaining>0)
ORDER BY S.SID, T.PIECE;