Logo Background

Script To Check Concurrent Process

  • By on November 3, 2009 | No Comments

    How to check the status of the Oracle concurrent process? Login to the database as APPS user and execute the SQL script as below and lots of vital information about the concurrent process status will be displayed.

    SELECT DECODE(SUM(DECODE(DECODE(s.status,'ACTIVE',s.serial#,0),
    l.serial#,1,0)),0,'No','Yes') "??",
              s.sid "Session ID",s.status "Status", s.sql_address "SQL Address",
              s.username "Username", RTRIM(s.osuser) "Oracle User", 
    s.module "Module", s.action "Type/Action", s.process "OS Process ID",
              b.spid "Oracle Process ID",s.machine "Machine Name",
              s.program  "Program",c.sql_text "SQL text", c.piece "piece"
       FROM v$session s, v$session_longops l,v$process b, v$sqltext c
           , apps.fnd_concurrent_requests fcr
     WHERE (s.sid = l.sid(+)) AND s.paddr=b.addr AND s.sql_address = c.address
       AND b.addr = s.paddr
       AND fcr.oracle_process_id = b.spid
       AND fcr.request_id = TO_NUMBER('20848420') -- put the Oracle Request ID here
     GROUP BY s.sid,s.status,s.sql_address,s.username,
    s.osuser,s.machine, s.program,b.spid, b.pid, c.sql_text,DECODE(s.serial#,l.serial#,1,0), 
    c.piece, s.module , s.action , s.process
     ORDER BY c.piece, s.sid
    » Log File Housekeeping Jobs
Leave a Comment