POUWIEL|COM

JeroenPouwiel

Retrieve background process name in SQL*Plus

I’ve been busy modifying my script which display all sessions in the database. I wanted to have the names of the background processes, not difficult i know. The trick is to get them in every (version of) database displayed correctly. Eventually i had it working:

[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> @ses
    Username                                OSUser       Blocker         ACTION         
------------------------------------------- ------------ --------------- --------------
A * ZZXJFF                     '136,9'      zzxjff                       SELECT         
A   __0)                       '156,3'      SYSTEM                                      
A   __1)                       '157,3'      SYSTEM                                      
A   __0)                       '162,1'      SYSTEM                                      
A   __T)                       '165,1'      SYSTEM                                      
A   __0)                       '167,1'      SYSTEM                                      
A   __0)                       '143,188'    SYSTEM                                      
A   __R)                       '166,1'      SYSTEM                                      
A   __N)                       '168,1'      SYSTEM                                      
A   __L)                       '160,1'      SYSTEM                                      
A   __N)                       '161,1'      SYSTEM                                      
A   __N)                       '170,1'      SYSTEM                                      
A   __0)                       '169,1'      SYSTEM                                      
A   __C)                       '149,1'      SYSTEM                                      
A   __0)                       '146,1'      SYSTEM                                      
A   __1)                       '133,1'      SYSTEM                                      
A   __O)                       '163,1'      SYSTEM                                      
A   __N)                       '164,1'      SYSTEM                                      

No biggie there. This is accomplished using:

DECODE(ses.username
           , NULL, '__'||RPAD(substr(prc.PROGRAM, 13, 4) ,15,' ')

The problem being, that when the host your’e on has a different naming convention than what your script anticipates, then you could well end up whith something like this:

[ZZXJFF@OCP03:W2ZZXJFF02]
SQL> @se
    Username                                OSUser       Blocker         ACTION
------------------------------------------- ------------ --------------- --------------
A * ZZXJFF                     '219,65073'  zzxjff                       SELECT
A   __C0)                      '125,1'      oracle
A   __C1)                      '257,1'      oracle
A   __Q0)                      '128,1'      oracle
A   __PT)                      '388,1'      oracle
A   __W0)                      '130,1'      oracle
A   __W1)                      '259,1'      oracle
A   __W2)                      '389,1'      oracle
A   __WR)                      '258,1'      oracle
A   __AN)                      '390,1'      oracle
A   __NL)                      '386,1'      oracle
A   __ON)                      '122,1'      oracle
A   __ON)                      '260,1'      oracle                       SELECT
A   __NC)                      '123,7'      oracle
A   __CO)                      '387,1'      oracle                       SELECT
A   __ON)                      '129,1'      oracle                       SELECT
A   __01)                      '241,55894'  oracle                       SELECT

Now, in 10g there’s a new function called REGEXP_SUBSTR that let’s you (amongst others) break down a string per word/value based on a delimiter. Like so:

[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> SELECT REGEXP_SUBSTR(program,'[^ ]+', 1, 2) RESULT
  2   from v$process;
RESULT
----------------------------------------------------------

(PMON)
(PSP0)
(MMAN)
(DBW0)
(LGWR)
(CKPT)
(SMON)
(RECO)
(CJQ0)
(MMON)
(MMNL)
(D000)
(S000)
(J000)
(ARC0)
(ARC1)
(QMNC)
(q000)
(SHAD)
(q001)

21 rows selected.

Now, what this does is, it extracts the 2nd field treating the ‘ ‘ (space) in ‘[^ ]+’ as a delimiter. Unfortunatly, this function only exists in 10g and up. In case of an earlier version try the following:

[ZZXJFF@OCP03:W2ZZXJFF02]
SQL> SELECT  SUBSTR(p.program,-5,4 ) RESULT
  2  FROM    V$PROCESS p
  3         ,V$SESSION s
  4  WHERE   s.paddr    =  p.addr(+)
  5  AND     s.username IS NULL
  6  /
RESULT
------
PMON
PSP0
MMAN
DBW0
LGWR
CKPT
SMON
RECO
CJQ0
MMON
MMNL
J000
ARC0
ARC1
QMNC
q000
q001

17 rows selected.

Comments are closed.

Categories