Scripts for Apps DBA

Important Queries for Apps DBA

Blocking Sessions

Connect as sysdba and execute below Query to find out blocking sessions

set lines 130

set pages 200

column module format a20

column program format a20

column username format a15

select s.sid,s.serial#,s.status,p.spid,s.module,s.action,s.program

from v$session s,v$process p

where s.sid in (select session_id from dba_locks where blocking_others='Blocking')

and s.paddr=p.addr

Note: Kill only INACTIVE blocking sessions. For Inactive blocking sessions,

Event will be as shown below:

SQL> select EVENT from gv$session_wait where sid=&sid;

Enter value for sid: 19

old   1: select EVENT from gv$session_wait where sid=&sid

new   1: select EVENT from gv$session_wait where sid=19

EVENT

----------------------------------------------------------------

SQL*Net message from client

Locks in Specific Modules

Locks in AP Module

column object_name format a40

SELECT a.object_id, a.session_id, b.object_name

FROM v$locked_object a, dba_objects b

WHERE a.object_id = b.object_id

AND b.owner in ('AP')

Another Query with more details

select s.sid sid_no,s.serial#,p.spid,s.action  action,a.user_name user_name,

to_char(b.start_time,'DD-MON-YYYY HH24:MI') stime

from fnd_user a, fnd_logins b, v$session s, v$process p

  where a.user_id = b.user_id

  and p.addr = s.paddr

  and s.process = b.spid

  and b.end_time is null

  and b.start_time > trunc(sysdate) - 1

  and a.user_name = '&login_id'

  and s.module like 'AP%'

Runaway Forms Sessions

set lines 130

set pages 200

column userinfo heading "ORACLE/OS User" format a25

column terminal heading "Term" format a6

column process heading "Parent|Process ID" format a10

column "sid (audsid)" format a20

column spid heading "Shadow|Process ID" format a10

column event heading "Waiting" format a30

select s.username||' '||s.osuser||' ('||decode(s.terminal,'unknown','?','UNKNOWN','?',s.terminal) ||')' userinfo,

s.sid||','|| s.serial# ||' ('|| s.audsid ||')' "sid (audsid)",   p.spid, w.event , w.p1

from v$session s, v$process p ,v$session_wait w

where p.addr = s.paddr and s.sid = w.sid and s.process = '&form_process_id' ;

Sample Output:

Enter value for form_process_id: 19721

old   5:      and s.process = '&form_process_id'

new 5:      and s.process = '19721'

                                               Shadow                 

ORACLE/OS User            sid (audsid)         Process ID                Waiting                                P1

------------------------- -------------------- ----------     ------------------------------ ----------

APPS applmgr ()           22,21485 (615657)    27872                    SQL*Net message from client    1952673792

Concurrent jobs Details

à List of Running Requests

set lines 130

set pages 200

col os form A7 head AppProc

col spid form a6 head SPID

col program form A50 trunc

set pages 38

col time form 99999.99 head Elapsed

col "Req Id" form 99999999

col "Parent" form a8

col "Prg Id" form 9999999

col serial# form 999999 head Serial#

col qname head "Manager" format a10 trunc

col sid format 9999 head SID

col user_name form A12 head User trunc

set recsep off

select

       q.concurrent_queue_name qname

      ,f.user_name

      ,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

--      ,vs.serial# "Serial#"

--      ,vp.spid

      ,a.oracle_process_id "spid"

      ,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"

      ,c.concurrent_program_name||' - '||

       c2.user_concurrent_program_name||' '||a.description "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

    ,APPLSYS.fnd_user f

--    ,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 c2.application_id = c.application_id

  and a.phase_code in ('I','P','R','T')

  and a.requested_by = f.user_id

  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 9 desc;

à List of pending Jobs waiting for managers:

set lines 130

col USER_CONCURRENT_QUEUE_NAME format a39

SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)

FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b

WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'

and a.Requested_Start_Date <= SYSDATE

AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND

STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003

and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID  

group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;

col program_description format a60

col user_concurrent_queue_name format a40

SELECT count(b.request_id) count, b.program_description, a.user_concurrent_queue_name

FROM apps.FND_CONCURRENT_QUEUES_VL a, apps.FND_CONCURRENT_WORKER_REQUESTS b

WHERE a.enabled_flag='Y'

AND  a.concurrent_queue_id = b.concurrent_queue_id

AND  (b.Phase_Code = 'P' OR b.Phase_Code = 'R') AND b.hold_flag != 'Y'

AND  b.Requested_Start_Date <= SYSDATE

AND  1=1

-- and a.user_concurrent_queue_name like 'Inventory%'

group by a.user_concurrent_queue_name, b.program_description order by 3,1 asc

à Long Running Requests (over a specified amount of time hard coded as 4 hours)

set lines 130

column start_time format a15

column USER_CONCURRENT_PROGRAM_NAME format a40

select b.REQUEST_ID, a.USER_CONCURRENT_PROGRAM_NAME, b.phase_code,

       (sysdate - b.actual_start_date) * 24 "running",

       to_char(b.request_date, 'mm/dd/yyyy hh24:mi') "request_date",

       to_char(b.actual_start_date, 'mm/dd/yyyy hh24:mi') "start_time"

   from APPS.FND_CONCURRENT_PROGRAMS_VL a,

       APPS.FND_CONCURRENT_REQUESTS b

 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID

   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID

   and b.STATUS_CODE = 'R'

   and b.PHASE_CODE = 'R'

   and ((sysdate - b.actual_start_date) * 24) > 4

   and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556);

à Total scheduled requests

set lines 130

select 'Scheduled requests:' schedt, count(*) schedcnt

from fnd_concurrent_requests

WHERE (requested_start_date > sysdate OR

status_code = 'P')

AND phase_code = 'P';

SELECT request_id id,

nvl(meaning, 'UNKNOWN') status,

user_concurrent_program_name pname,

to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,

to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd

FROM fnd_concurrent_requests fcr,

fnd_lookups fl,

fnd_concurrent_programs_vl fcpv

WHERE phase_code = 'P'

AND (fcr.requested_start_date >= sysdate OR

status_code = 'P')

AND LOOKUP_TYPE = 'CP_STATUS_CODE'

AND lookup_code = status_code

AND fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id

ORDER BY pname, request_date;

à pending requests Kept (on hold)

set lines 130

select 'Requests on hold:' schedt, count(*) schedcnt

from fnd_concurrent_requests

WHERE hold_flag = 'Y'

AND phase_code = 'P';

SELECT request_id id,

nvl(meaning, 'UNKNOWN') status,

user_concurrent_program_name pname,

to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd

FROM fnd_concurrent_requests fcr,

fnd_lookups fl,

fnd_concurrent_programs_vl fcpv

WHERE phase_code = 'P'

AND hold_flag = 'Y'

AND fcr.requested_start_date <= sysdate

AND status_code != 'P'

AND LOOKUP_TYPE = 'CP_STATUS_CODE'

AND lookup_code = status_code

AND fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id

ORDER BY request_date, request_id;

à List of pending Requests (used for Pending jobs threshold Monitor)

set lines 130

set pages 200

column REQUEST heading 'Request' format 9999999999

column PHASE heading 'Phase' format A8

column STATUS heading 'Status' format A8

column PROGRAM heading 'Program Name' format A40

column SHORT heading 'Short Name' format A15

column REQUESTOR heading 'Requestor' format A15

column START_TIME heading 'Start Time' format A15

column RUN_TIME justify left heading 'Time(m)' format 999999.9

column description format a75

 select  b.REQUEST_ID "Request", a.description,

       to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') "request_date",

       to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') "request_start"

  from APPS.FND_CONCURRENT_PROGRAMS_VL a,

       APPS.FND_CONCURRENT_REQUESTS b

 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID

   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID

   and b.PHASE_CODE = 'P'

   and b.HOLD_FLAG !='Y'

   and b.requested_start_date <= sysdate

à Steps to terminate a Concurrent request from backend

set lines 130

set pages 200

A)

select oracle_process_id ,

decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',       'Warning','T','Terminating')"Status_code",

 phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"

from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'

B)

select s.sid,s.serial#,module,s.status from v$session s,v$process p

where s.paddr=p.addr

and p.spid=&oracle_process_id

C)

update fnd_concurrent_requests

set  phase_code='C',

status_code='X'

where request_id=&Enter_REQUESTID;

commit;

Make sure that database session for the request id is killed using the output from step A and B.

à steps to Cancel pending jobs

update fnd_concurrent_requests

set phase_code='C',status_code='D'

WHERE phase_code = 'P' AND status_code in ('Q','I');

Commit;

à steps to Terminate pending jobs

update fnd_concurrent_requests

set phase_code='C',status_code='X'

WHERE phase_code = 'P' AND status_code in ('Q','I');

Commit;

à Query to find out list of NLS installed in Applications

set lines 130

select LANGUAGE_CODE from fnd_languages where INSTALLED_FLAG='I';

select NLS_LANGUAGE,LANGUAGE_CODE ,INSTALLED_FLAG from fnd_languages

where INSTALLED_FLAG in ('I','B');

à To get the list of installed products and patch level

set lines 130

set pages 400

col application_id      format 99990    heading "ID"

col application_name    format a40      heading "Name"

col application_prefix  format a6       heading "Prefix"

col application_short_name format a10   heading "Short name"

col apps                format a8       heading "Product"

col install_group_num   format 90       heading "Inst Grp"

col installed_flag      format a9       heading "Type"

col language_code       format a4       heading "Code"

col module_short_name   format a8       heading "Module"

col module_version      format a8       heading "Version"

col product_group_id    format 990      heading "ID"

col product_group_name  format a28      heading "Product Group Name"

col product_group_type  format a10      heading "Type"

col product_version     format a8       heading "Version"

col argument1           format a20      heading "Arguments"

col release_name        format a12      heading "Release"

col updated             format a11      heading "Updated"

col patchset            format a20      heading "Patchset Level"

col status              format a14      heading "Appl Status"

prompt --> Product Installation Status, Version Info and Patch Level

select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',

'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,

decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,

fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,

to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"

from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi

where fpi.application_id = a.application_id(+)

  and fpi.oracle_id = o.oracle_id(+)

order by 1,2;

àWorkflow Mailer configuration without login into OAM

set lines 130

set pages 200

col value format a30

select p.parameter_id,p.parameter_name,v.parameter_value value

from apps.fnd_svc_comp_param_vals_v v,

apps.fnd_svc_comp_params_b p,

apps.fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in

('OUTBOUND_SERVER', 'INBOUND_SERVER',

'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')

order by p.parameter_name;           

àScript to backup database links

connect to database as sysdba

set lines 130

set pages 200

select  'DB link for '||b.username||chr(10)||'  create  database  link '||c.name||' connect to '||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'

from dba_users b ,link$ c

where c.owner#=b.user_id

union

select  'DB link for Public' ||chr(10)|| '  create public  database  link '||c.name||' connect to

'||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'

from link$ c

where owner#=1;