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.