RE: How to Identify the BG processes?

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Tue, 25 Sep 2001 15:21:37 -0700
Message-ID: <F001.003986E6.20010925151601@fatcity.com>

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: Bruce.Reardon_at_comalco.riotinto.com.au

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: ListGuru_at_fatcity.com (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).

Received on Tue Sep 25 2001 - 17:21:37 CDT