Important Queries for Apps DBA 阻塞会话( blocking sessions) 以sysdba用户连接数据库,执行下面查询语句查找阻塞会话
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
特定模块的锁 AP模块的锁(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;