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;
