Do you mean how to find the sid, serial# of the background processes?
If so, join v$bgprocess with v$session.
Consider the following (originally from Deepak Thapliyal of this list,
modified a lot by me and including some code from Jared).
If you only want the background processes, change the outer join to
v$bgprocess to a normal join (AND a.paddr = bgp.paddr)
Regards,
Bruce Reardon
set pagesize 40
set linesize 500
SET FEEDBACK ON
col username format a14 wrap
col osuser format a11
col terminal format a10
col brief_program format a20 wrap HEADING 'Program'
col machine format a10
col type format a4 truncate
col logontime heading 'Logon-Start-Time' format a18
col "DOING?" format a8
col module format a10
col sid format 999
col serial# format 999999
COLUMN program FORMAT A65 WRAP HEADING "Path+Prog"
COLUMN idle_time HEADING "Idle Time"
--clear screen
SELECT
nvl2(bgp.name, ' ' || bgp.name , '') || nvl(a.username,'') username ,
a.osuser,
decode ( a.terminal , 'Windows NT PC' , a.machine , a.terminal ) Terminal
,
SUBSTR ( a.program ,
INSTR ( a.program , '\' , -1 ) + 1
) brief_program ,
type,
to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') logontime,
ltrim(rtrim(substr(b.name,1,10))) "DOING?",
substr(a.module,1,10) module,
a.sid,
a.serial# ,
-- idle time
-- days added to hours
--( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
-- days separately
substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
-- hours
substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time ,
vp.spid ,
a.process client_thread ,
a.program ,
nvl2(bgp.name, ' ' || bgp.name , '') || nvl(a.username,'') username
FROM
v$session a , sys.audit_actions b , v$bgprocess bgp , v$process vp
WHERE
b.action = a.command
AND a.paddr = bgp.paddr(+)
AND a.paddr = vp.addr(+)
ORDER BY
logon_time
;
-----Original Message-----
Sent: Tuesday, 25 September 2001 8:15
List,
In multiple database instances, how can i find the background processes of
appropriate instances?..
reGARDS,
nIRMAL
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
