How to find the SID, serial# and their operating system process ID from the database

A simple statement to find the SID, serial# and their operating system process ID from the database:

SET LINES 300 PAGES 300
SET TRIMSPOOL ON
SPOOL SQL
COL "Os pid" FORMAT A10
COL MACHINE  FORMAT A30
COL "SQL Text" FORMAT A100 WRA
COL PROGRAM  FORMAT A20
COL USERNAME FORMAT A12

SELECT
  S.SID,
  S.SERIAL#,
  S.USERNAME,
  S.STATUS,
  P.SPID "Os pid",
  S.MACHINE,
  CONCAT(SUBSTR(A.SQL_TEXT,1,60), '...(output truncated)')  "SQL text"
FROM V$SESSION S,
          V$SQLAREA A,
          V$PROCESS P
WHERE A.ADDRESS = S.SQL_ADDRESS
AND S.PADDR=P.ADDR
ORDER BY 1
/

Example output:

SID          SERIAL# USERNAME     Os pid     MACHINE          SQL text
---------- ---------- ------------ ---------- -------------  ------------------------------------------------------------------------------------
         4          3              6357052    myserver       insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated)
        18       7035 SYS          21496004   myserver       call DBMS_AQADM_SYS.REGISTER_DRIVER (  )...(output truncated)
       107      62143 SYS          23527460   myserver       SELECT   S.SID,   S.SERIAL#,   S.USERNAME,   P.SPID "Os pid"...(output truncated)
       303      27191 SYS          16777372   myserver       analyze table scott.man_to_stage validate structure cascade o...(output truncated)
       393          1              8716332    myserver       insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated)
       402       3125 BATCHUSR     18612294   myserver       call mypackage.start_dorg (  )...(output truncated)
       490          1              8257548    myserver       insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtim...(output truncated)
       499      25461 SYS          13762730   myserver       call DBMS_AQADM_SYS.REGISTER_DRIVER (  )...(output truncated)
       503       2635 SYS          11272234   myserver       call DBMS_AQADM_SYS.REGISTER_DRIVER (  )...(output truncated)
       593      12091 BATCHUSR     19726590   myserver       call myprocedure.read_queue (  )...(output truncated)
       600       9193 SYS          21102724   COMP\PC1       select * from dba_locks...(output truncated)
       694      44601 BATCHUSR     20840656   myserver       call mypackage.check_job_consistency(  )...(output truncated)
       696        819 SYS          15269968   myserver       call DBMS_AQADM_SYS.REGISTER_DRIVER (  )...(output truncated)
       700       1135 BATCHUSR     21364890   myserver       DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WIT...(output truncated)