Print Friendly, PDF & Email
What is a particular OS Oracle process (PID) executing in an Oracle database?
GOAL

To identify SID based on OS PID in the Oracle Database.

DEMO

DEMO: HOW TO MATCH OS PROCESS WITH ORACLE SESSION

SOLUTION

Execute the following query to match the OS PID with a database SID

SET LONG 200000000
SET LINES 300
SET PAGES 999
COL OSUSER FOR A6
COL DBUSER FOR A6
COL SID FOR 9999
COL SERIAL# FOR 9999999
COL PID FOR A8
COL SQL_ID FOR A15
COL STATUS FOR A10
COL CONNECTED_FROM FOR A20
COL MODULE FOR A10
COL PORT FOR 9999999
COL PROGRAM FOR A20
COL ACTIVE_TIME_SEC FOR 9999999999
COL PARSING_SCHEMA_NAME FOR A20 
COL SQL_FULLTEXT FOR A60

SELECT S.OSUSER,
       S.USERNAME DBUSER,
       S.SID,
       S.SERIAL#,
       P.SPID PID,
       S.SQL_ID,
       S.STATUS,
       S.MACHINE CONNECTED_FROM,
       S.PORT,
       S.PROGRAM,
       S.LAST_CALL_ET ACTIVE_TIME_SEC,
       Q.SQL_FULLTEXT
FROM
       V$SESSION S,
       V$PROCESS P,
       V$SQL Q
WHERE
       S.PADDR=P.ADDR AND
       S.SQL_ID=Q.SQL_ID AND 
       S.SQL_CHILD_NUMBER=Q.CHILD_NUMBER AND
       P.SPID=&PID;

Written At
08 JAN 202220:00
Red Hat Release
7.x x64
Database Release
19.13.0.0.0