Friday, December 7, 2012

Script to check the running Concurrent jobs
==============================
cmrun2.sql
========

set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 999999999
col "Parent" form a8
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,v$session vs
    ,v$process vp
where a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and a.phase_code in ('I','P','R','T')
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
  and vs.process (+) = b.os_process_id
  and vs.paddr = vp.addr (+)
order by 1,2
/

Or 
jobs.sql
=========
set pages 66
set line 132
-- spool progs1.lst
col     user_name       format a20 word_wrapped
column  ProgName        format a25 word_wrapped
column  requestId       format 9999999999
column  StartDate       format a20 word_Wrapped
column  OS_PROCESS_ID   format a6
column  ETime           format 99999999 word_Wrapped
col     sid             format 99999 word_Wrapped

select  sess.sid,
        oracle_process_id OS_PROCESS_ID,
        fusr.description user_name ,
        fcp.user_concurrent_program_name                progName,
        to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
        request_id                                                                                      RequestId,
        (sysdate - actual_start_date)*24*60*60 ETime
from    fnd_concurrent_requests fcr,
        fnd_concurrent_programs_tl  fcp,
        fnd_user fusr,
        v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
  and fcr.program_application_id        = fcp.application_id
  and fcp.language              = 'US'
  and fcr.phase_code    = 'R'
  and fcr.status_code   = 'R'
  and fcr.requested_by = fusr.user_id
  and fcr.oracle_session_id = sess.audsid (+)
--  and p.addr = sess.paddr
 order by 5 DESC
/
-- spool off

To check the running concurrent requests or Programs under particular Concurrent Manager
=============================================================
qdjobs.sql
=========
set pagesize 132
col USER_CONCURRENT_PROGRAM_NAME format a70 wrap
col StartDate format a20 wrap

SELECT
  REQUEST_ID,
  PHASE_CODE,
  STATUS_CODE,
  to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
  REQUESTED_BY,
  fcp.user_concurrent_program_name
FROM
  FND_CONCURRENT_WORKER_REQUESTS FCWR,
  FND_CONCURRENT_PROGRAMS_TL FCP
WHERE
  (Phase_Code = 'P' OR Phase_Code = 'R')
  AND hold_flag != 'Y'
  AND Requested_Start_Date <= SYSDATE
  AND ('' IS NULL OR ('' = 'B' AND  PHASE_CODE in ( 'R','P') AND  STATUS_CODE IN ('I', 'Q')))
 AND (CONCURRENT_QUEUE_ID  in (select CONCURRENT_QUEUE_ID FROM FND_CONCURRENT_QUEUES_VL WHERE USER_CONCURRENT_QUEUE_NAME like
   '%&manager_name%' ))
  --AND (QUEUE_APPLICATION_ID=0)
  AND FCP.CONCURRENT_PROGRAM_ID = FCWR.CONCURRENT_PROGRAM_ID
  and fcp.language      = 'US'
ORDER BY fcp.user_concurrent_program_name, STATUS_CODE,actual_Start_date,Priority, Priority_Request_ID, Request_ID
/

Concurrent Program previous history
===========================
conc_hist.sql
===========
set linesize 152
set pages 1000
col username for a10
col status for a10
col phase for a10
col PNAME for a15
col mins for 99999
col start_date for a18
col end_date for a18

col request_id for 99999999999
select
b.user_name username,
request_id as REQ_ID,
a.controlling_manager cm,
decode(a.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown') phase_code ,
decode(a.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'G','Warning',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
) status_code ,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
to_char(a.REQUESTED_START_DATE,'DD-MON-YY HH24-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24-MI-SS') END_DATE,
(a.ACTUAL_COMPLETION_DATE - a.REQUESTED_START_DATE)*24*60 "mins"
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
--status_code in ('E') and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
and rownum<100order by ACTUAL_COMPLETION_DATE;

To check the request details
======================
analreq.sql
=========
set serveroutput on size 10000
set feedback off
set verify off
set heading off
set timing off

variable        help_text  varchar2(2000);

prompt

DECLARE

req_id          number(15) := &1;



FUNCTION  get_status(p_status_code varchar2) return varchar2 AS

c_status        fnd_lookups.meaning%TYPE;

BEGIN
        SELECT nvl(meaning, 'UNKNOWN')
           INTO c_status
           FROM fnd_lookups
           WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
           AND LOOKUP_CODE = p_status_code;

        return rtrim(c_status);

END get_status;



FUNCTION  get_phase(p_phase_code varchar2) return varchar2 AS

c_phase         fnd_lookups.meaning%TYPE;

BEGIN
        SELECT nvl(meaning, 'UNKNOWN')
           INTO c_phase
           FROM fnd_lookups
           WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
           AND LOOKUP_CODE = p_phase_code;

        return rtrim(c_phase);

END get_phase;



PROCEDURE manager_check  (req_id        in  number,
                          cd_id         in  number,
                          mgr_defined   out boolean,
                          mgr_active    out boolean,
                          mgr_workshift out boolean,
                          mgr_running   out boolean,
                          run_alone     out boolean) is

    cursor mgr_cursor (rid number) is
      select running_processes, max_processes,
             decode(control_code,
                    'T','N',       -- Abort
                    'X','N',       -- Aborted
                    'D','N',       -- Deactivate
                    'E','N',       -- Deactivated
                        'Y') active
        from fnd_concurrent_worker_requests
        where request_id = rid
          and not((queue_application_id = 0)
                  and (concurrent_queue_id in (1,4)));

    run_alone_flag  varchar2(1);

  begin
    mgr_defined := FALSE;
    mgr_active := FALSE;
    mgr_workshift := FALSE;
    mgr_running := FALSE;

    for mgr_rec in mgr_cursor(req_id) loop
      mgr_defined := TRUE;
      if (mgr_rec.active = 'Y') then
        mgr_active := TRUE;
        if (mgr_rec.max_processes > 0) then
          mgr_workshift := TRUE;
        end if;
        if (mgr_rec.running_processes > 0) then
          mgr_running := TRUE;
        end if;
      end if;
    end loop;

    if (cd_id is null) then
      run_alone_flag := 'N';
    else
      select runalone_flag
        into run_alone_flag
        from fnd_conflicts_domain d
        where d.cd_id = manager_check.cd_id;
    end if;
    if (run_alone_flag = 'Y') then
      run_alone := TRUE;
    else
      run_alone := FALSE;
    end if;

  end manager_check;


PROCEDURE print_mgrs(p_req_id number) AS

CURSOR  c_mgrs(rid number) IS
        SELECT user_concurrent_queue_name name, fcwr.running_processes active,
        decode(fcwr.control_code,        'A', fl.meaning,
                                         'D', fl.meaning,
                                         'E', fl.meaning,
                                         'N', fl.meaning,
                                         'R', fl.meaning,
                                         'T', fl.meaning,
                                         'U', fl.meaning,
                                         'V', fl.meaning,
                                         'X', fl.meaning,
                                         NULL, 'Running',
                                         '** Unknown Status **') status
        FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
        WHERE fcwr.request_id = rid
        AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
        AND fcwr.concurrent_queue_id not in (1, 4)
        AND fl.lookup_code (+) = fcwr.control_code
        AND fl.lookup_type (+) = 'CP_CONTROL_CODE';

BEGIN

        for mgr_rec in c_mgrs(p_req_id) loop
            DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status
|| ' (' || mgr_rec.active || ' active processes)');
        end loop;

END print_mgrs;


PROCEDURE analyze_request(p_req_id number) AS

reqinfo         fnd_concurrent_requests%ROWTYPE;
proginfo        fnd_concurrent_programs_vl%ROWTYPE;

c_status        fnd_lookups.meaning%TYPE;
m_buf           fnd_lookups.meaning%TYPE;
conc_prog_name  fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name   fnd_application_vl.application_name%TYPE;
tmp_id          number(15);
tmp_status      fnd_concurrent_requests.status_code%TYPE;
tmp_date        date;
conc_app_id     fnd_concurrent_requests.program_application_id%TYPE;
conc_id         fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id      fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag  fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id       number(15);
resp_name       varchar2(100);
rclass_name     fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name   fnd_executables.execution_file_name%TYPE;

c_user          fnd_user.user_name%TYPE;
last_user       fnd_user.user_name%TYPE;

fcd_phase       varchar2(48);
fcd_status      varchar2(48);

traid           fnd_concurrent_requests.program_application_id%TYPE;
trcpid          fnd_concurrent_requests.concurrent_program_id%TYPE;

icount          number;
ireqid          fnd_concurrent_requests.request_id%TYPE;
pcode           fnd_concurrent_requests.phase_code%TYPE;
scode           fnd_concurrent_requests.status_code%TYPE;

live_child      boolean;
mgr_defined     boolean;
mgr_active      boolean;
mgr_workshift   boolean;
mgr_running     boolean;
run_alone       boolean;
reqlimit        boolean := false;

mgrname         fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
filename        varchar2(255);

qcf             fnd_concurrent_programs.queue_control_flag%TYPE;

apps_version    varchar2(3);

sep             varchar2(200) := '------------------------------------------------------';

REQ_NOTFOUND    exception;


CURSOR  c_wait IS
        SELECT request_id, phase_code, status_code
        FROM fnd_concurrent_requests
        WHERE parent_request_id = p_req_id;

CURSOR  c_inc IS
        SELECT to_run_application_id, to_run_concurrent_program_id
        FROM fnd_concurrent_program_serial
        WHERE running_application_id = conc_app_id
        AND running_concurrent_program_id = conc_id;

CURSOR  c_ireqs IS
        SELECT request_id, phase_code, status_code
        FROM   fnd_concurrent_requests
        WHERE  phase_code = 'R'
        AND    program_application_id = traid
        AND    concurrent_program_id = trcpid
        AND    cd_id = conc_cd_id;

CURSOR c_userreqs(uid number, s date) IS
       SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
              phase_code, status_code
       FROM fnd_concurrent_requests
       WHERE phase_code IN ('R', 'P')
       AND requested_by = uid
       AND requested_start_date < s
       AND hold_flag = 'N';

BEGIN

        BEGIN
            SELECT *
            INTO   reqinfo
            FROM   fnd_concurrent_requests
            WHERE  request_id = p_req_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                raise REQ_NOTFOUND;
        END;

        DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
        DBMS_OUTPUT.PUT_LINE(sep);


-- Program information
        DBMS_OUTPUT.PUT_LINE('Program information:');

        SELECT fvl.*
        INTO proginfo
        FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
        WHERE fcr.request_id = p_req_id
        AND fcr.concurrent_program_id = fvl.concurrent_program_id
        AND fcr.program_application_id = fvl.application_id;

        DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name
|| '  (' || proginfo.concurrent_program_name || ')');

        SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
        INTO conc_app_name
        FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
        WHERE fcr.request_id = p_req_id
        AND fcr.program_application_id = fvl.application_id;

        DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);

        SELECT nvl(meaning, 'UNKNOWN')
        INTO  m_buf
        FROM fnd_lookups
        WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
        AND lookup_code = proginfo.execution_method_code;

        SELECT nvl(execution_file_name, 'NONE')
        INTO exe_file_name
        FROM fnd_executables
        WHERE application_id = proginfo.executable_application_id
        AND executable_id = proginfo.executable_id;

        DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || '  (' || proginfo.execution_method_code || ')');
        DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
        DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
        DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
        DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
        DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);

        if proginfo.concurrent_class_id is not null then
                select request_class_name
                into rclass_name
                from fnd_concurrent_request_class
                where application_id = proginfo.class_application_id
                and request_class_id = proginfo.concurrent_class_id;

                DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
        end if;

        if proginfo.execution_options is not null then
                DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
        end if;

        if proginfo.enable_trace = 'Y' then
                DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
        end if;

        DBMS_OUTPUT.PUT_LINE(sep);
        DBMS_OUTPUT.PUT_LINE('
                             ');
        DBMS_OUTPUT.PUT_LINE(sep);

-- Submission information
        DBMS_OUTPUT.PUT_LINE('Submission information:');

        begin
                SELECT user_name into c_user from fnd_user
                where user_id = reqinfo.requested_by;
        exception
                when no_data_found then
                        c_user := '-- UNKNOWN USER --';
        end;

        begin
                SELECT user_name into last_user from fnd_user
                WHERE user_id = reqinfo.last_updated_by;
        exception
                when no_data_found then
                        last_user := '-- UNKNOWN USER --';
        end;

        DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
        SELECT responsibility_name
        INTO   resp_name
        FROM   fnd_responsibility_vl
        WHERE  responsibility_id = reqinfo.responsibility_id
        AND    application_id = reqinfo.responsibility_application_id;

        DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
        DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
        DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
                                 to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
        DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
        DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
        DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
        DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));

        DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);

        c_status := get_status(reqinfo.status_code);

        DBMS_OUTPUT.PUT_LINE(sep);
        DBMS_OUTPUT.PUT_LINE('
                             ');
        DBMS_OUTPUT.PUT_LINE(sep);

-- Analysis
        DBMS_OUTPUT.PUT_LINE('Analysis:');


-- Completed Requests
-------------------------------------------------------------------------------------------------------------
        IF reqinfo.phase_code = 'C' THEN



              DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
              DBMS_OUTPUT.PUT_LINE('It began running on: '||
                                   nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
                                                                          '-- NO START DATE --'));
              DBMS_OUTPUT.PUT_LINE('It completed on: '||
                                   nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
                                                                               '-- NO COMPLETION DATE --'));

              BEGIN
              SELECT user_concurrent_queue_name
              INTO   mgrname
              FROM   fnd_concurrent_queues_vl
              WHERE  concurrent_queue_id  =
                        (select CONCURRENT_QUEUE_ID
                           from fnd_concurrent_processes
                  where CONCURRENT_PROCESS_ID = reqinfo.controlling_manager);
              DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname);
              EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
              END;

              SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
              INTO   filename
              FROM   dual;
              DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
              SELECT nvl(reqinfo.outfile_name, '-- No output file --')
              INTO   filename
              FROM   dual;
              DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);

              DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
              DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));




-- Running Requests
-------------------------------------------------------------------------------------------------------------
        ELSIF reqinfo.phase_code = 'R' THEN



                DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
                DBMS_OUTPUT.PUT_LINE('It began running on: '||
                                         nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
                                                                                '-- NO START DATE --'));
                BEGIN
                SELECT user_concurrent_queue_name
                INTO   mgrname
                FROM   fnd_concurrent_queues_vl
                WHERE  concurrent_queue_id = reqinfo.controlling_manager;
                DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
                EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     null;
                END;

                SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
                INTO   filename
                FROM   dual;
                DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
                SELECT nvl(reqinfo.outfile_name, '-- No output file --')
                INTO   filename
                FROM   dual;
                DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);

                IF reqinfo.status_code = 'Z' THEN

                        -- Waiting request, See what it is waiting on
                        FOR child in c_wait LOOP

                                DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
                                                         child.request_id||' phase = '||get_phase(child.phase_code)||
                                                         ' status = '||get_status(child.status_code));
                        END LOOP;

                ELSIF reqinfo.status_code = 'W' THEN

                        -- Paused, check and see if it is a request set, and if its children are running
                        SELECT nvl(concurrent_program_name, 'UNKNOWN')
                        INTO conc_prog_name
                        FROM fnd_concurrent_programs
                        WHERE concurrent_program_id = reqinfo.concurrent_program_id;


                        DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
                        IF conc_prog_name = 'FNDRSSTG' THEN
                                DBMS_OUTPUT.PUT_LINE('This program is a Request Set Stage.');
                        END IF;

                        IF instr(conc_prog_name, 'RSSUB') > 0  THEN
                                 DBMS_OUTPUT.PUT_LINE('This program is a Request Set parent program.');
                        END IF;

                        live_child := FALSE;
                        FOR child in c_wait LOOP

                                DBMS_OUTPUT.PUT_LINE('It has a child request: '||
                                                         child.request_id||' (phase = '||get_phase(child.phase_code)||
                                                         ' - status = '||get_status(child.status_code)||')');
                                IF child.phase_code != 'C' THEN
                                        live_child := TRUE;
                                END IF;

                        END LOOP;

                        IF live_child = FALSE THEN
                                DBMS_OUTPUT.PUT_LINE('This request has no child requests
that are still running. You may need to wake this request up manually.');
                        END IF;
                END IF;



-- Pending Requests
-------------------------------------------------------------------------------------------------------------
        ELSIF reqinfo.phase_code = 'P' THEN


              DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
              DBMS_OUTPUT.PUT_LINE('                           (phase_code = P)   (status_code = '||reqinfo.status_code||')');

              -- could be a queue control request
              SELECT queue_control_flag
              INTO   qcf
              FROM   fnd_concurrent_programs
              WHERE  concurrent_program_id = reqinfo.concurrent_program_id
              AND    application_id = reqinfo.program_application_id;

              IF qcf = 'Y' THEN
                DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
                DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
                GOTO diagnose;
              END IF;

              -- why is it pending?

              -- could be scheduled
              IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
                 DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
                 DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
                                            to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
                 DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
                 GOTO diagnose;
              END IF;

              -- could be on hold
              IF reqinfo.hold_flag = 'Y' THEN
                DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
                DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on
'||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
                DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
                GOTO diagnose;
              END IF;

              -- could be disabled
              IF proginfo.enabled_flag = 'N' THEN
                 DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
                 DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name
||' needs to be enabled for this request to run.');
                 DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
                 GOTO diagnose;
              END IF;


              -- check queue_method_code
              -- unconstrained requests
              IF reqinfo.queue_method_code = 'I' THEN
                  DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
                  IF reqinfo.status_code = 'I' THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready
to be run by the next available manager.');
                  ELSIF reqinfo.status_code = 'Q' THEN
                      DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though
it is unconstrained. It will not be run by any manager.');
                  ELSIF reqinfo.status_code IN ('A', 'Z') THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
                      SELECT nvl(parent_request_id, -1)
                      INTO   parent_id
                      FROM   fnd_conc_req_summary_v
                      WHERE  request_id = p_req_id;
                      IF parent_id = -1 THEN
                          DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
                      ELSE
                          DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
                      END IF;
                  ELSE
                      DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
                  END IF;

              -- constrained requests
              ELSIF reqinfo.queue_method_code = 'B' THEN
                  DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
                  IF reqinfo.status_code = 'I' THEN
                      DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released
this request, and it is in a "Pending/Normal" status.');
                      DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
                  ELSIF reqinfo.status_code = 'Q' THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status.
The Conflict Resolution manager will need to release it before it can be run.');
                  ELSIF reqinfo.status_code IN ('A', 'Z') THEN
                      DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
                      SELECT nvl(parent_request_id, -1)
                      INTO   parent_id
                      FROM   fnd_conc_req_summary_v
                      WHERE  request_id = p_req_id;
                      IF parent_id = -1 THEN
                          DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
                      ELSE
                          DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
                      END IF;
                  ELSE
                      DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
                  END IF;


                  -- incompatible programs
                  SELECT program_application_id, concurrent_program_id, cd_id
                  INTO   conc_app_id, conc_id, conc_cd_id
                  FROM   fnd_concurrent_requests
                  WHERE  request_id = p_req_id;

                  icount := 0;
                  FOR progs in c_inc LOOP

                        traid :=  progs.to_run_application_id;
                        trcpid := progs.to_run_concurrent_program_id;

                        OPEN c_ireqs;
                        LOOP

                                FETCH c_ireqs INTO ireqid, pcode, scode;
                                EXIT WHEN c_ireqs%NOTFOUND;

                                DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is
waiting, or will have to wait, on an incompatible request: '|| ireqid );
                                DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
                                icount := icount + 1;


                        END LOOP;
                        CLOSE c_ireqs;


                  END LOOP;

                  IF icount = 0 THEN
                        DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
                  END IF;

                  -- could be a runalone itself
                  IF proginfo.run_alone_flag = 'Y' THEN
                      DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
                  END IF;

                  -- single threaded
                  IF reqinfo.single_thread_flag = 'Y' THEN
                      DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Sequential Requests is set.');
                      reqlimit := true;
                  END IF;

                  -- request limit
                  IF reqinfo.request_limit = 'Y' THEN
                      DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Active Request Limit is set.');
                      reqlimit := true;
                  END IF;

                  IF reqlimit = true THEN
                     DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
                     FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
                        DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id ||
' Requested start date: ' || progs.start_date);
                        DBMS_OUTPUT.PUT_LINE('     Phase: ' || get_phase(progs.phase_code)
|| '   Status: ' || get_status(progs.status_code));
                     END LOOP;
                  END IF;

              -- error, invalid queue_method_code
              ELSE
                  DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
                  DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
                  GOTO diagnose;
              END IF;


              DBMS_OUTPUT.PUT_LINE(sep);
              DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');

              -- check the managers
              manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);

              -- could be a runalone ahead of it
              IF run_alone = TRUE THEN
                    DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');

                    select user_cd_name into conflict_domain from fnd_conflicts_domain
                    where cd_id = reqinfo.cd_id;

                    DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);

                    -- see what is running
                    begin
                    select request_id, status_code, actual_start_date
                    into tmp_id, tmp_status, tmp_date
                    from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
                    where fcp.run_alone_flag = 'Y'
                    and fcp.concurrent_program_id = fcr.concurrent_program_id
                    and fcr.phase_code = 'R'
                    and fcr.cd_id = reqinfo.cd_id;

                        DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
                                                 ', which is running with status '||get_status(tmp_status));
                        DBMS_OUTPUT.PUT_LINE('It has been running since: '||
                                         nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
                    exception
                       when NO_DATA_FOUND then
                           DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
                                                        ', but there is no runalone request running');
                    end;

              ELSIF mgr_defined = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each
manager to make sure they are defined correctly.');
              ELSIF mgr_active = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There are one or more managers defined
that can run this request, but none of them are currently active');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    -- print out which managers can run it and their status
                    DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
                    print_mgrs(p_req_id);
              ELSIF mgr_workshift = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running
in an active workshift that can run this request');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    -- display details about the workshifts
              ELSIF mgr_running = FALSE THEN
                    DBMS_OUTPUT.PUT_LINE('There is one or more managers available
to run this request, but none of them are running');
                    DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
                    -- print out which managers can run it and their status
                    print_mgrs(p_req_id);
              ELSE
                    -- print out the managers available to run it
                    DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
                    print_mgrs(p_req_id);

              END IF;


        -- invalid phase code
        ELSE
             DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');

        END IF;

<<diagnose>>
        BEGIN
        FND_CONC.DIAGNOSE(p_req_id, fcd_phase, fcd_status, :help_text);
        EXCEPTION
            WHEN OTHERS THEN
                :help_text := 'The FND_CONC package has not been installed on this system.';
        END;

        DBMS_OUTPUT.PUT_LINE(sep);

EXCEPTION
        WHEN REQ_NOTFOUND THEN
            DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
            DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);

END analyze_request;


BEGIN

analyze_request(req_id);



END;
/

To Check the session details
======================
sess.sql
==========
def aps_prog    = 'sessinfo.sql'
def aps_title   = 'Session information'

col "Session Info" form A100
set verify off
accept sid      prompt 'Please enter the value for Sid if known            : '
accept terminal prompt 'Please enter the value for terminal if known       : '
accept machine  prompt 'Please enter the machine name if known             : '
accept process  prompt 'Please enter the value for Client Process if known : '
accept spid     prompt 'Please enter the value for Server Process if known : '
accept osuser   prompt 'Please enter the value for OS User if known        : '
accept username prompt 'Please enter the value for DB User if known        : '
select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||
       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||
       s.machine||'  -  '|| s.terminal||chr(10)||
       '        OS Process Ids : '||
       s.process||' (Client)  '||p.spid||' (Server)'|| ' (Since) '||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
       '   Client Program Name : '||s.program||chr(10) ||
           '   Action / Module     : '||s.action||'  / '||s.module||chr(10) || chr(10)  ||
       '   Wait Status         : '||s.event || ' ' || s.seconds_in_wait || ' ' || s.state "Session Info"
  from v$process p,v$session s
 where p.addr = s.paddr
   and s.sid = nvl('&SID',s.sid)
   and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
   and nvl(s.process,-1) = nvl('&Process',nvl(s.process,-1))
   and p.spid = nvl('&spid',p.spid)
   and s.username = nvl('&username',s.username)
   and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
   and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
   and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
       nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/
undefine sid

To check the current running all sessions with wait events
=======================================
waits.sql
==========
set lines 132
set pagesize 1000
column          sid format 99999
column          event format a25 word_wrapped
column          module format a15 word_wrapped
column      username format a9 word_wrapped
column      seconds_in_wait format 999999 word_wrapped
select b.sid, b.event, substr(a.action,1,1)||'-'||a.module module , a.username ,
      b.p1, b.p2,b.p3 ,b.seconds_in_wait from v$session_wait b, v$session  a
where b.event not in ('SQL*Net message from client','rdbms ipc message',
'pmon timer','smon timer', 'pipe get')
  and a.sid  = b.sid
order by 8
/

whywait.sql
==========
col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status
  from v$session_wait a, v$session s
 where a.sid=s.sid
 and a.sid=&SID
/

To fetch the running sql from sid
========================
getsql.sql
=========
break on hash_value
set pagesize 1000
set long 200000
select hash_value, sql_text
from v$sqltext
where hash_value in
        (select sql_hash_value from
                v$session where sid = &sid)
order by piece
/

To check the session row movement
========================
sessrow.sql
==========
column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'
/

To fetch the explain plan from the running session
==================================
expl.sql
=======
--variable   sqlid varchar2(13);
column sql_id new_value sqlid
 select sql_id from v$session where sid=&sid
/
select plan_table_output from table(DBMS_XPLAN.display_cursor('&sqlid'))
/

You can also run explain plan for in sql propmt and provide sql  and check explain plan using utlxpls.sql
=======================================================================
sql> explain plan for
    select name from v$database;

explained
sql> @utlxpls.sql  --to view the explain plan for particular sql stmt

utlxpls.sql
========
set markup html preformat on

Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

To check the TEMP usage
==================
sortsize.sql
========

col used format 999,999,999,999
col free format 999,999,999,999
col maxused format 999,999,999,999
col totalsize format 999,999,999,999

SELECT tablespace_name, (TOTAL_BLOCKS * 8192) "totalsize",
(USED_BLOCKS * 8192 ) "used" , (FREE_BLOCKS * 8192) "free" ,
(MAX_USED_BLOCKS * 8192 ) "maxused"
FROM v$sort_segment;

psort.sql   --to check the temp usage sessions 
=========
col module format a15
col username format a15
col tablespace format a15
col size format 999,999,999,999
set lines 120
SELECT s.sid,s.username,s.module,u.contents,segtype,TABLESPACE, sum((u.blocks * 8192)) "size"
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
and u.extents > 10
group by s.sid,s.username,s.module,u.contents,segtype,tablespace
order by 7,2,3;

To check the inactive form sessions
==========================
inactive_frm.sql
==============
set pages 200 lines 200
SELECT a.sid,a.serial#, substr(b.object_name,1,40) object_name,
DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || '  Min', round((d.last_call_et/60)/60) || '  HRS' ) "Lock Time"
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || '  Min', round((d.last_call_et/60)/60) || '  HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;

inactive_frm_kill.sql
==============
set pages 999
set head off
select 'alter system kill session '''||a.sid||','||a.serial#||''''||' immediate;'
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || '  Min', round((d.last_call_et/60)/60) || '  HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;

To check the stale objects 
====================
stale.sql
=========
set pages 2000
set linesize 200
set heading off
set feedback off
select 'exec fnd_stats.gather_table_stats(ownname =>'''||owner||''',tabname =>'''||table_name||''',percent => 30, cascade => TRUE);'
from dba_tab_statistics where stale_stats='YES'
and owner not in ('SYS','SYSTEM','TRCANLZR','OLAPSYS','XDB')
and table_name not like 'BIN%' order by 1;

To check the Patch details including patch stage
=================================
patch_details.sql
============
set line 132
col patch_top for a30
col name for a10
select a.PATCH_TOP,
c.DRIVER_FILE_NAME,
b.name,
a.CREATION_DATE
from
AD_PATCH_RUNS a,
AD_APPL_TOPS b,
AD_PATCH_DRIVERS C
where
a.APPL_TOP_ID=b.APPL_TOP_ID
and c.PATCH_DRIVER_ID=a.PATCH_DRIVER_ID
and c.DRIVER_FILE_NAME like '%&Patch_Name%'
order by a.CREATION_DATE, c.DRIVER_FILE_NAME;

To Kill toad/sql developer sessions
=========================
select 'alter system disconnect session '''||sid||','||serial#||'''immediate;' from v$session where program like  '%T.O.A.D%' or program in('plsqldev.exe','SQL Developer','TOAD.exe','PLSQLDev.exe');

To check ASM disk utilization
=======================
select name, state, sum(total_mb/1024), sum(free_mb/1024) from v$asm_diskgroup where  name like upper('%&Instance_name%') group by name,state;

Users details from machine
=====================
user_AT.sql
=========
col  machine for a25
select count(distinct d.user_name) ,b.machine  from apps.fnd_logins a,
  v$session b, v$process c, apps.fnd_user d
   where b.paddr = c.addr
and a.pid=c.pid
  and a.spid = b.process
  and d.user_id = a.user_id
  and (d.user_name = 'USER_NAME' OR 1=1)  group by b.machine;

Patch details including language
========================

col  PATCH_NAME for a15
select a.PATCH_NAME, a.APPLIED_PATCH_ID, b.LANGUAGE from ad_applied_patches a, ad_patch_driver_langs b where a.APPLIED_PATCH_ID=b.PATCH_DRIVER_ID and a.PATCH_NAME like '%&PatchNumber%';

To check the ICM status
=================
select apps.fnd_oam_em.get_icm_status from dual;

To check the FND Profiles
======================
set lines 200
col  PROFILE_OPTION_NAME for a25
col  PROFILE_OPTION_VALUE for a65
select a.profile_option_name, b.profile_option_value, a.profile_option_id, a.application_id from fnd_profile_options a, fnd_profile_option_values b where a.application_id = b.application_id and a.profile_option_id = b.profile_option_id
and b.profile_option_value like '%&value%';

To fetch the trace file name from request
==============================
req_tracefile.sql
============
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 90
set pages 22
set head off
select 'Request id: '||request_id , 'Trace   id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace, 'Trace Name: '||dest.value||
'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
 'Prog. Name: '||prog.user_concurrent_program_name,
'File  Name: '||execname.execution_file_name|| execname.subroutine_name ,
 'Status    : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
 'SID Serial: '||ses.sid||','|| ses.serial#,
'Module    : '||ses.module from
fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname
where  req.request_id = &request and  req.oracle_process_id=proc.spid(+) and
proc.addr = ses.paddr(+) and dest.name='user_dump_dest' and    dbnm.name='db_name' and
req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and    prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;

To check the RMAN backup details
===========================
rman_bkp_dtl.sql
===============
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-3
order by session_key;

dbms_rman_job.sql
===============
select job_name,job_type,number_of_arguments,job_action,repeat_interval,enabled from dba_scheduler_jobs where JOB_NAME like  '%RMAN%';

dbms_rman_pgm.sql
================
select OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,NUMBER_OF_ARGUMENTS,ENABLED from dba_scheduler_programs where  PROGRAM_NAME like '%RMAN%';

To check the locks
===============
lock_waits.sql --to check dead locks with details
===============
set serveroutput on size 10000
declare
   cursor c1 is
   select * from v$lock where request != 0
   order by id1, id2;
   wid1            number := -999999;
   wid2            number := -999999;
   wholder_detail  varchar2(200);
   v_err_msg          varchar2(80);
   wsid            number(5);
   wstep           number(2);
   wtype           varchar2(10);
   wobject_name    varchar2(180);
   wobject_name1   varchar2(80);
   wlock_type      varchar2(50);
   w_lastcallet  varchar2(11);
   h_lastcallet  varchar2(11);
   cursor c2 (v_id1 v$lock.ID1%TYPE,v_id2 v$lock.ID2%TYPE) is select sid , type from v$lock
   where id1     = v_id1
     and id2     = v_id2
     and request = 0
     and lmode  != 4;
begin
   for c1_rec in c1 loop
      if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
         null;
      else
         wstep  := 10;
         /* 
         select sid , type into wsid , wtype
         from v$lock
         where id1  = c1_rec.id1
           and id2  = c1_rec.id2
           and request = 0
           and lmode != 4;
         */
         for c2_rec in c2(c1_rec.id1,c1_rec.id2) loop
            wsid:=c2_rec.sid;
            wtype:=c2_rec.type;
           dbms_output.put_line('  ');
           wstep  := 20;
           select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
           into wholder_detail
           from v$session s, v$process p
           where s.sid= wsid
          and s.paddr = p.addr;
          dbms_output.put_line(wholder_detail);
          begin
             select decode(wtype,'TX', 'Transaction',
                            'DL', 'DDL Lock',
                            'MR', 'Media Recovery',
                            'RT', 'Redo Thread',
                            'UN', 'User Name',
                            'TX', 'Transaction',
                            'TM', 'DML',
                            'UL', 'PL/SQL User Lock',
                            'DX', 'Distributed Xaction',
                            'CF', 'Control File',
                            'IS', 'Instance State',
                            'FS', 'File Set',
                            'IR', 'Instance Recovery',
                            'ST', 'Disk Space Transaction',
                            'TS', 'Temp Segment',
                            'IV', 'Library Cache Invalida-tion',
                            'LS', 'Log Start or Switch',
                            'RW', 'Row Wait',
                            'SQ', 'Sequence Number',
                            'TE', 'Extend Table',
                            'TT', 'Temp Table',
                            'Un-Known Type of Lock')
                into wlock_type
                from dual;
             declare
                cursor c3 is
                select object_id from v$locked_object
                where session_id = wsid;
             begin
                wobject_name := '';
                for c3_rec in c3 loop
                   select object_type||': '||owner||'.'||object_name
                   into wobject_name
                   from dba_objects
                   where object_id = c3_rec.object_id;
                   wobject_name := wobject_name ||' '||wobject_name1;
                end loop;
             exception
                when others then
                wobject_name := wobject_name ||' No Object Found';
             end;
             dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
          exception
             when no_data_found then
             dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
          end;
         end loop; 
      end if;
      wstep  := 30;
      select '....   Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                ' SID:' || s.sid || ' Status: ' || s.status  ||
                ' (' ||         floor(last_call_et/3600)||':'||
                                floor(mod(last_call_et,3600)/60)||':'||
                                mod(mod(last_call_et,3600),60) ||
                ') Module:'|| module ||
              ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
      into wholder_detail
      from v$session s, v$process p
      where s.sid= c1_rec.sid
      and s.paddr = p.addr;
      dbms_output.put_line(wholder_detail);
      wid1  := c1_rec.id1;
      wid2  := c1_rec.id2;
   end loop;
   if wid1 = -999999 then
      wstep  := 40;
      dbms_output.put_line('No one requesting locks held by others');
   end if;
exception
   when others then
   v_err_msg := (sqlerrm ||'  '|| sqlcode||' step='||to_char(wstep));
   DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/

lckdtl.sql --to chk blocking locks
=========
set feedback on
set lines 132
column sess format a8 word_wrapped
column id1   format 99999999
column id2   format 99999999
column req       format 999
column type  format a4
column event format a15 word_wrapped
column seconds_in_wait format 999999
column status format a10
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(vl.request,0,0,5),' ')||vl.sid sess
       , vl.id1
       , vl.id2
       , vl.lmode
       ,  vl.request req, vl.type,
        sw.event event, sw.seconds_in_wait, s.status
FROM V$LOCK vl, v$session_wait sw, v$session s
 WHERE vl.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
  and vl.sid = sw.sid
  and vl.sid = s.sid
 ORDER BY id1,request
/

locks.sql-To check all the locks in the database
========
 set pagesize 1000
set line 132
break on "SID"
col Object format a35 wrap
col Locker format a10
col Type format  a10
col Program format a30 wrap
col Lockmode format a15 wrap
col Wait format 9999999
col Status format a8

SELECT
 SESSION_ID "SID",
 OWNER||'.'||OBJECT_NAME "Object",
  ORACLE_USERNAME "Locker",
-- nvl(lockwait,mysessf(lockwait)) "Wait",
  lockwait "Wait",
  DECODE(LOCKED_MODE,
    2, 'ROW SHARE',
    3, 'ROW EXCLUSIVE',
    4, 'SHARE',
    5, 'SHARE ROW EXCLUSIVE',
    6, 'EXCLUSIVE',  'UNKNOWN') "Lockmode",
  OBJECT_TYPE "Type",
  c.ROW_WAIT_ROW#,
  c.Status "Status"
FROM
  SYS.V_$LOCKED_OBJECT A,
  SYS.ALL_OBJECTS B,
  SYS.V_$SESSION c
WHERE
  A.OBJECT_ID = B.OBJECT_ID AND
  C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/

To check the particular user sessions details
==============================
user_sess.sql
==========
def aps_prog    = 'sessinfo.sql'
def aps_title   = 'Session information'

col "Session Info" form A80
col dbuser form A8
col mach form A8
col program format a20 trunc
col description format a20 trunc
col module format a10 wrap
col action format a10 wrap
set verify off
set lines 132
select s.sid , substr(s.username,1,8) dbuser, substr(s.machine,1,10) mach, s.process,p.spid,f.login_type
           ,s.action,s.module,to_char(start_time,'DD-MON-YY HH24:MI') stime,
      fu.description
  from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
 where p.addr = s.paddr
   and f.spid (+)  = s.process
   and fu.user_id (+) = f.user_id
   and f.end_time(+) is null
   and fu.user_name='&user_name'
order by f.start_time
/

To kill particular user sessions
=====================
set pages 0
set lines 132
!rm /tmp/user_sessions.sql
prompt Here is a list of SID's that correspond to the user entered:
set feedback off
spool /tmp/user_sessions.sql
prompt set echo on
set head off
set pages 100
set linesize 255
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';'
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
 where p.addr = s.paddr
   and f.spid (+)  = s.process
   and fu.user_id (+) = f.user_id
   and f.end_time(+) is null
   and fu.user_name='&user_name'
order by f.start_time;
prompt set echo off
spool off
!chmod 777 /tmp/user_sessions.sql
set feedback on
prompt Verify file @/tmp/user_sessions.sql and run it to kill them .
set head on
set echo off

To kill particular sessions
=================
undefine sid
set serveroutput on
--set heading off
DECLARE
CURSOR get_ps  IS
   SELECT distinct sid,serial# from v$session
   where sid in (&sid);

ps  get_ps%ROWTYPE;

sql_cmd       VARCHAR2(1000);
sql_cur       INTEGER;
sql_row_cnt   INTEGER;

BEGIN

    sql_cur := dbms_sql.open_cursor;

    OPEN get_ps;
    LOOP

       FETCH get_ps INTO ps;
       EXIT WHEN get_ps%NOTFOUND;

         dbms_output.put_line ('Killing session '||ps.sid||','||ps.serial#);
         sql_cmd := 'ALTER SYSTEM KILL SESSION ' ||''''||ps.sid||','|| ps.serial#||'''';
        -- dbms_output.put_line ( sql_cmd );
         dbms_sql.parse ( sql_cur , sql_cmd , dbms_sql.v7 );
         sql_row_cnt := dbms_sql.execute ( sql_cur );
         dbms_output.put_line ( sql_cmd );

    END LOOP;

    EXCEPTION

      WHEN NO_DATA_FOUND THEN
        dbms_sql.close_cursor ( sql_cur );

END;
/

To check the long ops
==================
set verify off
col opname format a40 trunc
col pctdone format 999
col mintogo format 9,999.90
accept trgtsid number default 0 prompt 'Limit to which SID : '
select s.sid,o.opname,s.sql_hash_value,o.sofar,o.totalwork,o.elapsed_seconds,
        round(o.time_remaining/60,2) mintogo,
        round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)
/

or

select sid, opname, target, sofar, totalwork, units,
time_remaining, elapsed_seconds, message, username,
to_date(last_update_time,'dd-MON-yy hh:mi:ss')
from v$session_longops
where sid=&sid
/

Library Cache pin locks
================
libcachepin.sql
============
select /*+ ordered */ w1.sid waiting_session,h1.sid holding_session,w.kgllktype lock_or_pin,w.kgllkhdl
address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;
prompt To find out the object being referred, Please run the following select script;
prompt -----------------------------------------------------------------------------;
prompt select distinct to_name from v$object_dependency where to_address = '<address>';
prompt -----------------------------------------------------------------------------;

To check the DB Links
----------------------
set lines 132
set pages 100
col owner format a10
col db_link format a15
col username format a10
col host format a50
select * from dba_db_links
/






Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)