Tip of the Week
Tip for Week of December 7, 2003

Instance Background Processes Information

This tip comes from Galina Petrenko, Sr. IT Specialist, TPAS Systems Development, at Towers Perrin in Voorhees, New Jersey.

-- Oracle 8I
select
        A.SID,
        A.SERIAL#,
        A.PROGRAM,
        P.PID,
        P.SPID,
        A.OSUSER,       /* Who Started INSTANCE */
        A.TERMINAL,
        A.MACHINE,
        A.LOGON_TIME,
        B.NAME,
        B.Description
from
        v$session       A,
        v$process       P,
        v$bgprocess     B
where
        A.PADDR=B.PADDR
AND     A.PADDR=P.ADDR
and     A.type='BACKGROUND'
;


--Output-- ( UNIX )
----------

SID SERIAL# PROGRAM             OSUSER PID SPID  TERMINAL MACHINE
LOGON_TIME  NAME DESCRIPTION           
  1       1 oracle@sunO817 (PMON) oracle   2 24782 UNKNOWN  sunO817
15-Nov-2003 PMON process cleanup       
  2       1 oracle@sunO817 (DBW0) oracle   3 27864 UNKNOWN  sunO817
15-Nov-2003 DBW0 db writer process 0   
  3       1 oracle@sunO817 (LGWR) oracle   4 28380 UNKNOWN  sunO817
15-Nov-2003 LGWR Redo etc.             
  4       1 oracle@sunO817 (CKPT) oracle   5 28638 UNKNOWN  sunO817
15-Nov-2003 CKPT checkpoint            
  5       1 oracle@sunO817 (SMON) oracle   6 28896 UNKNOWN  sunO817
15-Nov-2003 SMON System Monitor Process
  6       1 oracle@sunO817 (RECO) oracle   7 29154 UNKNOWN  sunO817
15-Nov-2003 RECO distributed recovery  
  9       1 oracle@sunO817 (ARC0) oracle  10 29928 UNKNOWN  sunO817
15-Nov-2003 ARC0 Archival Process 0    
 22     585 oracle@sunO817 (ARC1) oracle  16 53922 UNKNOWN  sunO817
16-Nov-2003 ARC1 Archival Process 1    




-- Oracle9I+
select
        A.SID,
        A.SERIAL#,
        A.PROGRAM,
        P.PID,
        P.SPID,
        A.OSUSER,       /* Who Started INSTANCE */
        A.TERMINAL,
        A.MACHINE,
        A.LOGON_TIME,
        B.NAME,
        B.Description
        ,P.PGA_USED_MEM
        ,P.PGA_FREEABLE_MEM
        ,P.PGA_MAX_MEM
from
        v$session       A,
        v$process       P,
        v$bgprocess     B
where
        A.PADDR=B.PADDR
AND     A.PADDR=P.ADDR
--and   A.type='BACKGROUND'
--Alternative (you can use BACKGROUND column from v$process )
--------------
AND     P.BACKGROUND=1
;


--Output-- ( Win2k )
----------

SID SERIAL# PROGRAM    OSUSER PID SPID TERMINAL MACHINE  LOGON_TIME NAME
DESCRIPTION           
  1       1 ORACLE.EXE SYSTEM   2 1312 ADVTEST0 ADVTEST0 20-Nov-03  PMON
process cleanup       
  2       1 ORACLE.EXE SYSTEM   3 972  ADVTEST0 ADVTEST0 20-Nov-03  DBW0
db writer process 0   
  3       1 ORACLE.EXE SYSTEM   4 1276 ADVTEST0 ADVTEST0 20-Nov-03  LGWR
Redo etc.             
  4       1 ORACLE.EXE SYSTEM   5 1228 ADVTEST0 ADVTEST0 20-Nov-03  CKPT
checkpoint            
  5       1 ORACLE.EXE SYSTEM   6 792  ADVTEST0 ADVTEST0 20-Nov-03  SMON
System Monitor Process
  6       1 ORACLE.EXE SYSTEM   7 1448 ADVTEST0 ADVTEST0 20-Nov-03  RECO
distributed recovery