[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select q.user_concurrent_queue_name srvc_name,
q.concurrent_queue_id conc_queue_id,
a.application_id appl_id,
a.application_name srvc_app_name,
a.application_short_name srvc_app_short_name,
q.concurrent_queue_name srvc_short_name,
s.service_handle srvc_handle
from fnd_concurrent_queues_vl q, fnd_application_vl a,
fnd_cp_services s
where q.application_id = a.application_id
and s.service_id = q.manager_type;
SELECT to_char(ready),
to_char(waiting),
to_char(processed),
to_char(expired),
to_char(undeliverable),
to_char(errored)
FROM dual;
SELECT foa.metric_short_name,
decode(fcq.user_concurrent_queue_name,NULL,'N/A',fcq.user_concurrent_queue_name),
decode(fcq.description,NULL,'N/A',fcq.description),
decode(foa.name,NULL,'N/A',foa.name),
to_char(foa.status_code),
decode(foa.node_name,NULL,'N/A',foa.node_name),
decode(fl.meaning,NULL,'N/A',fl.meaning) platform,
to_char(foa.last_update_date,'yyyy/MM/dd HH:mm:ss') last_update_date,
decode(foa.type,NULL,'N/A',foa.type),
decode(fcq.manager_type,NULL,'N/A',fcq.manager_type)
FROM fnd_oam_app_sys_status foa,
fnd_concurrent_queues_vl fcq,
FND_OAM_FNDNODES_VL fn,
fnd_lookups fl
WHERE foa.application_id = fcq.application_id (+)
AND foa.concurrent_queue_name = fcq.concurrent_queue_name (+)
AND upper(foa.node_name) = upper(fn.node_name (+))
AND nvl(fn.platform_code, '-x') = fl.lookup_code(+)
AND fl.lookup_type(+) = 'PLATFORM';
LAST_UPDATE_DATE VARCHAR2(256);
SELECT fom.METRIC_SHORT_NAME metric_name,
fom.METRIC_VALUE value,
to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
FROM fnd_oam_metval fom
WHERE fom.METRIC_SHORT_NAME in
('PATCHES','PROFILE_OPT','CONTEXT_FILES_EDITED','INVALID_OBJECTS')
)
LOOP
EXIT WHEN SQL%NOTFOUND;
LAST_UPDATE_DATE:=pairs.last_update;
SELECT PATCHES,
PROFILE_OPT,
CONTEXT_FILES_EDITED,
INVALID_OBJECTS,
LAST_UPDATE_DATE
FROM dual;
LAST_UPDATE_DATE VARCHAR2(256);
SELECT fom.METRIC_SHORT_NAME metric_name,
decode(to_char(fom.status_code),NULL,'NA','2','DOWN','0','UP','1','WARNING','3','NOT_STARTED','NA') value,
to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
FROM fnd_oam_metval fom
WHERE fom.GROUP_ID = 8
)
LOOP
EXIT WHEN SQL%NOTFOUND;
LAST_UPDATE_DATE:=pairs.last_update;
SELECT PL_SQL_AGNT_GEN,
SERVLET_AGNT_GEN,
JSP_AGNT_GEN,
JTF_GEN,
DISCOVERER_GEN,
PHP_GEN,
TCF_GEN,
LAST_UPDATE_DATE
FROM dual;
LAST_UPDATE_DATE VARCHAR2(256);
SELECT fom.METRIC_SHORT_NAME metric_name,
fom.METRIC_VALUE value,
to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
FROM fnd_oam_metval fom
WHERE fom.METRIC_SHORT_NAME in
('CRIT_UNPR_EXCEP','CRIT_PR_EXCEP','CRIT_TOTAL_UNPR_EXCEP','OPEN_OCC')
)
LOOP
EXIT WHEN SQL%NOTFOUND;
LAST_UPDATE_DATE:=pairs.last_update;
SELECT CRIT_UNPR_EXCEP,
CRIT_PR_EXCEP,
CRIT_TOTAL_UNPR_EXCEP,
OPEN_OCC,
LAST_UPDATE_DATE
FROM dual;
( SELECT ( decode(substr(profile_option_value,length(profile_option_value)), '/', substr(profile_option_value,1,length(profile_option_value)-1), profile_option_value)
||'/servlets/weboam/') p_v
FROM fnd_profile_options_vl ovl,
fnd_profile_option_values v
WHERE (v.level_id(+) = 10001)
AND (ovl.application_id = 0 or ovl.application_id = 178)
AND ovl.profile_option_name='APPS_FRAMEWORK_AGENT'
AND ovl.application_id = v.application_id(+)
AND ovl.profile_option_id=v.profile_option_id(+)
)
LOOP
oam_root_url := p_r_pair.p_v;
( select APPLICATIONS_SYSTEM_NAME apps_name,
RELEASE_NAME version,
MULTI_ORG_FLAG m_org,
decode(MULTI_LINGUAL_FLAG,null,' ',MULTI_LINGUAL_FLAG) m_li,
MULTI_CURRENCY_FLAG m_cur
from fnd_product_groups
)
LOOP
apps_name:=p_r_pair.apps_name;
( select to_char(count(*)) ac
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 and status <>'N'
)
LOOP
products_installed := p_r_pair.ac;
SELECT fnd_oam_em.get_apps_level apps_level,
oam_root_url,
apps_name,
apps_version,
multi_org,
multi_lingual,
multi_currency,
products_installed
FROM dual;
SELECT to_char(count(F.login_id))
INTO ACTIVE_USERS
FROM fnd_login_resp_forms F,
gv$session S
WHERE F.AUDSID = S.AUDSID;
SELECT to_char(count(*))
INTO SERVICE_PROCS
FROM fnd_concurrent_processes
WHERE process_status_code in ('R','A','P','C','M','D','T');
SELECT to_char(count(*))
INTO RUNNING_REQ
FROM fnd_concurrent_requests
WHERE phase_code = 'R';
SELECT ACTIVE_USERS,
SERVICE_PROCS,
RUNNING_REQ
FROM dual;
( SELECT count(*) p_v
FROM wf_notifications
WHERE mail_status in ('ERRORED','FAILED')
)
LOOP
errored_ct := pair_1.p_v;
( SELECT count(*) p_v
FROM wf_notifications
WHERE mail_status in ('UNSENT')
)
LOOP
unsent_ct := pair_2.p_v;
SELECT to_char(errored_ct),to_char(unsent_ct)
FROM dual;
( SELECT foa.status_code sta
FROM fnd_oam_app_sys_status foa,
fnd_concurrent_queues_vl fcq,
FND_OAM_FNDNODES_VL fn,
fnd_lookups fl
WHERE foa.application_id = fcq.application_id (+)
AND foa.concurrent_queue_name = fcq.concurrent_queue_name (+)
AND upper(foa.node_name) = upper(fn.node_name (+))
AND nvl(fn.platform_code, '-x') = fl.lookup_code(+)
AND fl.lookup_type(+) = 'PLATFORM'
AND ( foa.metric_short_name='WEB_SERVER_OVERALL'
OR foa.metric_short_name='ADMIN_SERVER_OVERALL'
OR foa.metric_short_name='DATA_SERVER_OVERALL'
OR foa.metric_short_name='FORMS_SERVER_OVERALL'
OR foa.metric_short_name='CP_SERVER_OVERALL'
)
)
LOOP
EXIT WHEN SQL%NOTFOUND;
SELECT to_char(status)
FROM dual;
SELECT to_char(count(*))
FROM ( select distinct user_id from icx_sessions
where last_connect >= sysdate - (1/24)
-- and (function_type is null or function_type != 'FORM')
);
select asn,an,to_char(sum(nac)),to_char(sum(ac)) from
(
SELECT a.application_short_name asn, a.application_name an, COUNT(*) nac, 0 ac
FROM fnd_concurrent_requests r, fnd_application_vl a
WHERE r.program_application_id = a.application_id
AND phase_code in ('P', 'R')
AND r.resubmit_time is null
GROUP BY a.application_short_name,a.application_name
UNION ALL
SELECT a.application_short_name asn, a.application_name an, 0 nac, COUNT(*) ac
FROM fnd_concurrent_requests r, fnd_application_vl a
WHERE r.program_application_id = a.application_id
AND phase_code in ('P', 'R')
AND r.resubmit_time is not null
GROUP BY a.application_short_name,a.application_name
)
group by asn,an;
SELECT to_char(a.a), to_char(b.b), to_char(c.c),
a.a/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100,
b.b/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100,
c.c/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100
FROM
(
SELECT COUNT(*) a
FROM fnd_concurrent_requests
WHERE phase_code = 'C' AND status_code = 'C'
AND actual_completion_date between (sysdate - 1/24) and sysdate
) a,
(
SELECT COUNT(*) b
FROM fnd_concurrent_requests
WHERE phase_code = 'C' AND status_code = 'G'
AND actual_completion_date between (sysdate - 1/24) and sysdate
) b,
(
SELECT COUNT(*) c
FROM fnd_concurrent_requests
WHERE phase_code = 'C' AND status_code = 'E'
AND actual_completion_date between (sysdate - 1/24) and sysdate
) c ;
( SELECT (profile_option_value||'/servlets/weboam/') p_v
FROM fnd_profile_options_vl ovl,
fnd_profile_option_values v
WHERE (v.level_id(+) = 10001)
AND (ovl.application_id = 0 or ovl.application_id = 178)
AND ovl.profile_option_name='APPS_FRAMEWORK_AGENT'
AND ovl.application_id = v.application_id(+)
AND ovl.profile_option_id=v.profile_option_id(+)
)
LOOP
frame_agent := p_r_pair.p_v;
SELECT frame_agent
FROM dual;
( SELECT count(*) prc, status_code
FROM fnd_concurrent_requests
WHERE status_code IN ('I', 'Q' )
AND requested_start_date <= sysdate
AND phase_code = 'P'
AND hold_flag = 'N'
GROUP BY status_code
)
LOOP
IF p_r_pair_1.status_code = 'I' THEN
pending_normal_ct := p_r_pair_1.prc;
( SELECT count(*) prc
FROM fnd_concurrent_requests
WHERE phase_code = 'P'
AND hold_flag = 'N'
AND ( (status_code = 'P' )
OR ( status_code IN( 'I', 'Q')
AND requested_start_date > sysdate
)
)
)
LOOP
pending_scheduled_ct := p_r_pair_2.prc;
( SELECT count(*) prc
FROM fnd_concurrent_requests
WHERE status_code IN( 'R', 'T')
)
LOOP
running_ct := p_r_pair_3.prc;
( SELECT count(*) prc
FROM fnd_concurrent_requests
WHERE phase_code = 'P' AND status_code = 'M'
)
LOOP
no_manager_ct := p_r_pair_4.prc;
( SELECT count(*) prc
FROM fnd_concurrent_requests
WHERE phase_code = 'P' AND hold_flag = 'Y'
)
LOOP
on_hold_ct := p_r_pair_5.prc;
SELECT to_char(pending_normal_ct),
to_char(pending_standby_ct),
to_char(pending_scheduled_ct),
to_char(no_manager_ct),
to_char(on_hold_ct),
to_char(running_ct)
FROM dual;
( SELECT user_concurrent_queue_name r_n,
fnd_conc_request_pkg.running_requests(concurrent_queue_name,
application_id) r_c,
(select count(*)
from fnd_concurrent_processes cp
where cp.queue_application_id = cq.application_id
and cp.concurrent_queue_id = cq.concurrent_queue_id
and (cp.process_status_code in ('C','M')
or (cp.process_status_code in ('A', 'D', 'T')
and exists (select 1 from gv$session
where cp.session_id = audsid)
)
)
) a_p,
fnd_oam_em.get_pend_rqsts('I',application_id,concurrent_queue_id) n_c,
fnd_oam_em.get_pend_rqsts('Q',application_id,concurrent_queue_id) s_c
FROM fnd_concurrent_queues_vl CQ,
WHERE manager_type = 1
ORDER BY decode(CQ.control_code, 'X',2,'E',2,1), user_concurrent_queue_name
)
LOOP
EXIT WHEN SQL%NOTFOUND;
sql_stmt := 'SELECT count(R.Request_ID) rc '||
' FROM Fnd_concurrent_worker_requests '||
' WHERE phase_code = ''P'''||
' AND status_code = :status_code'||
' AND queue_application_id = :app_id'||
' AND concurrent_queue_id = :mgr_id'||
' AND hold_flag != ''Y'''||
' AND CWR.requested_start_date <= sysdate';
SELECT count(distinct(item_key)) ac
FROM wf_items
WHERE end_date is null)
LOOP
EXIT WHEN SQL%NOTFOUND;
SELECT count(*) ac
FROM wf_item_activity_statuses
WHERE activity_status = 'DEFERRED')
LOOP
EXIT WHEN SQL%NOTFOUND;
SELECT count(*) ac
FROM wf_item_activity_statuses
WHERE activity_status = 'SUSPEND')
LOOP
EXIT WHEN SQL%NOTFOUND;
SELECT count(distinct(item_key)) ac
FROM wf_item_activity_statuses
WHERE activity_status = 'ERROR')
LOOP
EXIT WHEN SQL%NOTFOUND;
SELECT to_char(active_count),
to_char(deferred_count),
to_char(suspended_count),
to_char(errored_count)
FROM dual;
SELECT hs.audsid, hs.program, hs.osuser, hs.process,
hs.machine, hs.terminal,
hl.ctime,
hl.type, to_char(hl.id1), to_char(hl.id2),
DECODE(hl.lmode, 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',
4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?')
FROM fnd_concurrent_processes p, v$session ws, v$lock wl, v$lock hl ,
v$session hs
WHERE p.queue_application_id = 0 AND p.concurrent_queue_id IN (1,4)
AND p.session_id = ws.audsid AND ws.lockwait IS NOT NULL
AND ws.lockwait = wl.kaddr AND wl.id1 = hl.id1 AND wl.id2 = hl.id2
AND hl.sid = hs.sid AND hl.request = 0;
SELECT
fsm.metric_short_name,
fsm.metric_display_name,
fsm.metric_value,
decode(fsm.description,NULL,'N/A',fsm.description),
to_char(decode(fsm.status_code,NULL,-1,fsm.status_code)),
to_char(fsm.last_update_date,'yyyy/MM/dd HH:mm:ss') last_update_date,
to_char(fsm.sequence),
to_char(fsm.group_id),
to_char(fsmg.sequence),
fsmg.metric_group_display_name
FROM fnd_oam_metval_vl fsm,
fnd_oam_met_grps_vl fsmg,
FND_OAM_FNDNODES_VL fn
WHERE fsm.group_id = fsmg.metric_group_id
AND upper(fsm.node_name) = upper(fn.node_name (+))
ORDER BY fsmg.sequence, fsm.sequence;