[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_global.conc_request_id into v_conc_req_id from dual;
select fnd_global.conc_request_id into v_conc_req_id from dual;
select fcr.requested_by into v_userid
from fnd_concurrent_requests fcr
where fcr.request_id = v_conc_req_id;
select fnd_global.user_id into v_userid from dual;
select nvl(is_supported,'Y'), nvl(collection_enabled_flag,'Y')
into v_is_supported, v_collection_enabled_flag
from fnd_oam_metval
where metric_short_name = p_metric_short_name
and rownum = 1;
select nvl(collection_enabled_flag, 'Y')
into v_collection_enabled_flag
from fnd_oam_svci_info
where application_id = p_application_id
and concurrent_queue_name = p_concurrent_queue_name;
select metric_type, threshold_operator, threshold_value
into l_metric_type, l_threshold_operator, l_threshold_value
from fnd_oam_metval
where metric_short_name = p_metric_short_name
and rownum = 1;
v_check_sql := 'select 1 from fnd_oam_metval where metric_short_name = :1 and '||
v_value_column||' '||l_operator_symbol ||' '||'('||l_threshold_value||')';
v_check_sql := 'select 1 from fnd_oam_metval where metric_short_name = :1 and '||
v_value_column||' '||l_operator_symbol ||' '||':2';
select threshold_value
into l_threshold_value
from fnd_oam_svci_info
where application_id = p_application_id
and concurrent_queue_name = p_concurrent_queue_name;
v_check_sql := 'select 1 from fnd_oam_app_sys_status where ' ||
'application_id = :1 and concurrent_queue_name = :2 and ' ||
'status_code IN ' || '('||l_threshold_value||')';
select pov.profile_option_value
into v_url
from fnd_profile_options po,
fnd_profile_option_values pov
where po.profile_option_name = 'APPS_SERVLET_AGENT'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and pov.level_id = 10001;
select pov.profile_option_value
into v_launcher
from fnd_profile_options po,
fnd_profile_option_values pov
where po.profile_option_name = 'ICX_REPORT_LAUNCHER'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and pov.level_id = 10001;
select pov.profile_option_value
into v_server
from fnd_profile_options po,
fnd_profile_option_values pov
where po.profile_option_name = 'ICX_REPORT_SERVER'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and pov.level_id = 10001;
select pov.profile_option_value
into v_launcher
from fnd_profile_options po,
fnd_profile_option_values pov
where po.profile_option_name = 'ICX_FORMS_LAUNCHER'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and pov.level_id = 10001;
PROCEDURE insert_app_sys_status_internal (
p_metric_short_name in varchar2,
p_application_id number,
p_concurrent_queue_short_name varchar2,
p_name varchar2,
p_type varchar2,
p_status_code in number,
p_node_name in varchar2)
IS
v_userid number;
insert into fnd_oam_app_sys_status (metric_short_name, application_id,
concurrent_queue_name, name, type, status_code, node_name, last_updated_by,
last_update_date, last_update_login)
values
(p_metric_short_name,
p_application_id,
p_concurrent_queue_short_name,
p_name,
p_type,
p_status_code,
p_node_name,
v_userid, sysdate, 0);
END insert_app_sys_status_internal;
PROCEDURE update_metric_internal (
p_metric_name in varchar2,
p_value in varchar2,
p_status_code in number)
IS
v_userid number;
update fnd_oam_metval
set metric_value = p_value,
status_code = p_status_code,
last_collected_date = sysdate,
last_updated_by = v_userid,
last_update_date = sysdate,
last_update_login = 0
where metric_short_name = p_metric_name;
update fnd_oam_metval
set metric_value = p_value,
last_collected_date = sysdate,
last_updated_by = v_userid,
last_update_date = sysdate,
last_update_login = 0
where metric_short_name = p_metric_name;
END update_metric_internal;
select
fcq.application_id application_id,
fcq.concurrent_queue_id concurrent_queue_id,
fcq.concurrent_queue_name concurrent_queue_name
from fnd_cp_services fcs, fnd_concurrent_queues fcq
where
fcs.service_id = to_number(fcq.manager_type)
and fcs.server_type in (v_server_type, 'E')
and upper(fcq.target_node) = upper(v_node_name)
and upper(fcs.enabled) = 'Y'
and upper(fcq.enabled_flag) = 'Y'
order by fcs.oam_display_order asc;
insert_app_sys_status_internal(
p_server_type || '_' || to_char(v_count) ||
'_' || p_node_name,
svc_inst.application_id, svc_inst.concurrent_queue_name, null,
p_server_type,v_status_code, p_node_name);
select status_code from fnd_oam_app_sys_status
where metric_short_name like p_server || '%';
insert_app_sys_status_internal(
p_server || '_OVERALL',
null, null, null, p_server_type, overall_st, null);
select status_code from fnd_oam_app_sys_status where
upper(node_name) = upper(p_node_name) and (
metric_short_name like p_server_type || '_%');
select status_code into v_host_status
from fnd_oam_app_sys_status
where upper(node_name) = upper(p_node_name)
and metric_short_name like 'HOST_%'
and rownum = 1; -- we expect only one row per host
insert_app_sys_status_internal(
v_server_type_prep || p_node_name,
null, null, null, p_server_type, v_tier_overall_status, p_node_name);
select upper(node_name) node_name, status, support_cp, support_forms,
support_web, support_admin
from FND_OAM_FNDNODES_VL
where node_mode = 'O'
and (nvl(support_cp, 'N') = 'Y' or
nvl(support_forms, 'N') = 'Y' or
nvl(support_web, 'N') = 'Y' or
nvl(support_admin, 'N') = 'Y' or
nvl(support_db, 'N') = 'Y');
delete from fnd_oam_app_sys_status;
select decode(nvl(node.status, 'U'),
'Y',STATUS_NORMAL,
'N',STATUS_ERROR,
'U',STATUS_UNKNOWN, STATUS_UNKNOWN) into node_status
from dual;
insert_app_sys_status_internal(
'HOST_'||to_char(i),
null, null, null, null, node_status, node.node_name);
select upper(host_name) host_name, instance_name, database_status
from gv$instance;
select upper(node_name) node_name,upper(webhost) webhost from FND_OAM_FNDNODES_VL
where node_mode = 'O'
and (nvl(support_cp, 'N') = 'Y' or
nvl(support_forms, 'N') = 'Y' or
nvl(support_web, 'N') = 'Y' or
nvl(support_admin, 'N') = 'Y' or
nvl(support_db, 'N') = 'Y');
select decode(instr(db.host_name, '.') - 1, -1,
db.host_name,
substr(db.host_name, 0,
instr(db.host_name, '.') - 1))
into v_gv_host from dual;
select decode(instr(ndx.node_name, '.') - 1, -1,
ndx.node_name,
substr(ndx.node_name, 0,
instr(ndx.node_name, '.') - 1))
into v_fn_node from dual;
select decode(instr(ndx.webhost, '.') - 1, -1,
ndx.webhost,
substr(ndx.webhost, 0,
instr(ndx.webhost, '.') - 1))
into v_fn_webhost from dual;
select 1 into v_temp
from fnd_oam_app_sys_status
where metric_short_name like 'HOST_%'
and node_name = db.host_name;
select count(*) into v_host_count
from fnd_oam_app_sys_status
where metric_short_name like 'HOST_%'
and node_name is not null;
insert_app_sys_status_internal(
'HOST_'||to_char(v_host_count+1),
null, null, null, null,
STATUS_UNKNOWN, db.host_name);
select 1 into v_temp
from fnd_oam_app_sys_status
where metric_short_name like 'HOST_%'
and node_name = v_node_name;
select count(*) into v_db_count
from fnd_oam_app_sys_status
where metric_short_name like 'DATABASE_INS_%'
and node_name = v_node_name;
insert_app_sys_status_internal(
'DATABASE_INS_' || to_char(v_db_count+1) || '_' || v_node_name,
null, null, db.instance_name, 'D', v_db_status, v_node_name);
select node_name from fnd_oam_app_sys_status
where metric_short_name like 'HOST_%'
and node_name is not null;
select metric_short_name, status_code
from fnd_oam_app_sys_status
where metric_short_name like 'DATABASE_INS_%'
and node_name = p_node;
insert_app_sys_status_internal(
'DATA_SERVER_' || nd.node_name,
null, null, null, 'D', v_db_status, nd.node_name);
select count(distinct(F.login_id))
into ct_active_users
from fnd_login_resp_forms F,
gv$session S
where F.AUDSID = S.AUDSID;
update_metric_internal('ACTIVE_USERS', to_char(ct_active_users), -1);
select count(*) into ct_db_sessions from gv$session where audsid > 0;
update_metric_internal('DB_SESSIONS', to_char(ct_db_sessions), -1);
select count(*)
into ct_running_req
from fnd_concurrent_requests
where phase_code = 'R';
update_metric_internal('RUNNING_REQ', to_char(ct_running_req), -1);
select count(*)
into ct_service_processes
from fnd_concurrent_processes
where process_status_code in ('R','A','P');
update_metric_internal('SERVICE_PROCS', to_char(ct_service_processes), -1);
select count(concurrent_queue_id)
into ct_services_up
from fnd_concurrent_queues_vl
where running_processes = max_processes and max_processes > 0;
update_metric_internal('SERVICES_UP', to_char(ct_services_up), 0);
select count(concurrent_queue_id)
into ct_services_down
from fnd_concurrent_queues_vl
where running_processes = 0 and max_processes > 0;
update_metric_internal('SERVICES_DOWN', to_char(ct_services_down), 2);
SELECT COUNT(*)
into ct_invalid_objects
FROM DBA_OBJECTS DO
WHERE DO.STATUS = 'INVALID' AND
DO.OWNER = user and
EXISTS (select 1
from DBA_ERRORS DE
where DE.NAME = DO.OBJECT_NAME AND
DE.OWNER = DO.OWNER );
update_metric_internal('INVALID_OBJECTS', to_char(ct_invalid_objects), -1);
select nvl(alert_enabled_flag,'Y'),
threshold_operator, threshold_value
into v_alrt_enabled_flag, v_threshold_oper, v_threshold_val
from fnd_oam_metval
where metric_short_name = 'WFM_WAIT_MSG';
select count(*)
into ct_waiting_msg
from
(
select mail_status
from wf_notifications
where mail_status = 'MAIL' ) v
where rownum <= to_number(v_threshold_val) + 1;
select count(*)
into ct_waiting_msg
from wf_notifications
where mail_status = 'MAIL';
update_metric_internal('WFM_WAIT_MSG', to_char(ct_waiting_msg), -1);
select count(*) into ct_patches
from ad_patch_drivers d, ad_patch_runs r
where r.end_date >= sysdate - 1
and d.patch_driver_id = r.patch_driver_id;
update_metric_internal('PATCHES', to_char(ct_patches), -1);
select count(*) into ct_profile_options
from fnd_profile_options ovl,
fnd_profile_option_values v
where ovl.start_date_active <= SYSDATE
and (nvl(ovl.end_date_active, SYSDATE) >= SYSDATE)
and (v.level_id = 10001 and v.level_value = 0)
and ovl.profile_option_id = v.profile_option_id
and ovl.application_id = v.application_id
and (sysdate - v.last_update_date <= 1);
update_metric_internal('PROFILE_OPT', to_char(ct_profile_options), -1);
select count(*) into ct_context_files
from (
select focf.last_update_date lud
from fnd_oam_context_files focf
where (status <> 'H' or status is null)
and upper(name) <> 'METADATA')
where lud >= sysdate - 1;
update_metric_internal('CONTEXT_FILES_EDITED', to_char(ct_context_files), -1);
select count(*) into v_numerator
from fnd_concurrent_requests
where phase_code in ('R', 'C')
and status_code <> 'D'
and greatest(requested_start_date, request_date)
between sysdate-1 and sysdate;
select count(*) into v_denominator
from fnd_concurrent_requests
where ( (phase_code in ('R', 'C')
and status_code <> 'D')
or ( status_code in ('I','Q')
and hold_flag <> 'Y') )
and greatest(requested_start_date, request_date)
between sysdate-1 and sysdate;
select round((greatest(1,v_numerator)/greatest(1,v_denominator))* 100)
into ct_completed_req
from dual;
update_metric_internal('COMPLETED_REQ', to_char(ct_completed_req), -1);
select count(*)
into ct_processed_msg
from wf_notifications
where mail_status = 'SENT'
and status = 'OPEN'
and (sysdate - begin_date <= 1);
update_metric_internal('WFM_PROC_MSG', to_char(ct_processed_msg), -1);
select count(*) into ct_new_al from fnd_log_unique_exceptions where
status='N' and category='USER';
update_metric_internal('USER_ALERT_NEW', to_char(ct_new_al), -1);
select count(*) into ct_new_occ
from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
where fle.unique_exception_id = flue.unique_exception_id
and flue.status='N'
and flue.category='USER';
update_metric_internal('USER_ALERT_NEW_OCC', to_char(ct_new_occ), -1);
select count(*) into ct_open_al from fnd_log_unique_exceptions where
status='O' and category='USER';
update_metric_internal('USER_ALERT_OPEN', to_char(ct_open_al), -1);
select count(*) into ct_open_occ
from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
where fle.unique_exception_id = flue.unique_exception_id
and flue.status='O'
and flue.category='USER';
update_metric_internal('USER_ALERT_OPEN_OCC', to_char(ct_open_occ), -1);
select count(*) into ct_new_al from fnd_log_unique_exceptions where
status='N';
update_metric_internal('CRIT_UNPR_EXCEP', to_char(ct_new_al), -1);
select count(*) into ct_new_occ
from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
where fle.unique_exception_id = flue.unique_exception_id
and flue.status='N';
update_metric_internal('CRIT_PR_EXCEP', to_char(ct_new_occ), -1);
select count(*) into ct_open_al from fnd_log_unique_exceptions where
status='O';
update_metric_internal('CRIT_TOTAL_UNPR_EXCEP', to_char(ct_open_al), -1);
select count(*) into ct_open_occ
from fnd_log_exceptions fle, fnd_log_unique_exceptions flue
where fle.unique_exception_id = flue.unique_exception_id
and flue.status='O';
update_metric_internal('OPEN_OCC', to_char(ct_open_occ), -1);
update_metric_internal(v_agent_short_name, null, v_status_code);
select metric_short_name, metric_type, metric_value, status_code,
threshold_operator, threshold_value
from fnd_oam_metval
where nvl(is_supported,'Y') = 'Y'
and nvl(collection_enabled_flag,'Y') = 'Y'
and nvl(alert_enabled_flag, 'N') = 'Y'
and group_id <> 8 and group_id <> 0; -- Disabling Alerting for Web Components and Internal Metrics
select foa.application_id application_id,
foa.concurrent_queue_name concurrent_queue_name,
fcq.concurrent_queue_id concurrent_queue_id,
foa.status_code status_code
from fnd_oam_app_sys_status foa,
fnd_concurrent_queues fcq,
fnd_oam_svci_info fsi
where foa.application_id = fcq.application_id
and foa.concurrent_queue_name = fcq.concurrent_queue_name
and foa.application_id = fsi.application_id (+)
and foa.concurrent_queue_name = fsi.concurrent_queue_name (+)
and nvl(fsi.collection_enabled_flag, 'Y') = 'Y'
and nvl(fsi.alert_enabled_flag, 'N') = 'Y';
'select fnd_oam_dashboard_util.get_trans_name_values(''MET'','''||v_metric_list||''') from dual');
'select fnd_oam_dashboard_util.get_trans_name_values(''STATUS'','''||v_st_list||''') from dual');
select
fcr.request_id request_id
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where
fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'FNDOAMCOL'
and fcr.phase_code = 'P';
select application_id, responsibility_id, responsibility_key
into appl_id, resp_id, resp_key
from fnd_responsibility
where responsibility_key = 'SYSTEM_ADMINISTRATOR';
select user_id, user_name
into user_id, user_name
from fnd_user
where user_name = 'SYSADMIN';
select count(*) into v_in_progress_count
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where
fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'FNDOAMCOL'
and fcr.phase_code in ('R','P')
and fcr.resubmit_interval is not null
and fcr.resubmit_interval_unit_code is not null;
select fcr.resubmit_interval, fcr.resubmit_interval_unit_code
into v_curr_interval, v_curr_unit_code
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where
fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'FNDOAMCOL'
and fcr.phase_code in ('R','P')
and fcr.resubmit_interval is not null
and fcr.resubmit_interval_unit_code is not null;
select
fcr.request_id request_id
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where
fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name = 'FNDOAMCOL'
and fcr.phase_code in ('P','R');
select 1 into v_temp
from fnd_log_unique_exceptions
where encoded_message = p_enc_msg
and status in ('N','O');
select fcr.request_id request_id,
fcp.user_concurrent_program_name user_concurrent_program_name
from
fnd_concurrent_requests fcr,
fnd_conc_prog_onsite_info fcpoi,
fnd_concurrent_programs_vl fcp
where
fcr.program_application_id=fcpoi.program_application_id
AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and fcr.concurrent_program_id=fcpoi.concurrent_program_id
and fcpoi.avg_run_time is not null
and (sysdate -fcr.ACTUAL_START_DATE)*86400 >
(fcpoi.avg_run_time*(1+.01*to_number(p_tol)))
and ((sysdate -fcr.ACTUAL_START_DATE)*86400 >
(to_number(p_offset)*60))
and fcr.phase_code='R';
select threshold_value, alert_enabled_flag
into v_lng_run_req_count, v_lng_run_req_alert_enable
from fnd_oam_metval
where metric_short_name ='LONG_RUNNING_REQ_COUNT';
select threshold_value into v_lng_run_req_tolerance
from fnd_oam_metval
where metric_short_name ='LONG_RUNNING_REQ_TOLERANCE';
select threshold_value into v_lng_run_req_offset
from fnd_oam_metval
where metric_short_name ='LONG_RUNNING_REQ_OFFSET';
select fcr.request_id request_id,
fcp.user_concurrent_program_name user_concurrent_program_name,
fcpoi.max_run_time max_run_time,
fcpoi.avg_run_time avg_run_time,
fcpoi.alert_long_running_threshold/60 threshold_minutes,
fcpoi.alert_long_running_tolerance tolerance
from
fnd_concurrent_requests fcr,
fnd_conc_prog_onsite_info fcpoi,
fnd_concurrent_programs_vl fcp
where
fcr.program_application_id=fcpoi.program_application_id
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
and fcr.concurrent_program_id=fcpoi.concurrent_program_id
and ((fcpoi.ALERT_LONG_RUNNING_THRESHOLD is not null)
or (fcpoi.AVG_RUN_TIME is not null))
and (sysdate -fcr.ACTUAL_START_DATE)*86400 >
(to_number(nvl(fcpoi.ALERT_LONG_RUNNING_THRESHOLD,
fcpoi.AVG_RUN_TIME))*(1+.01*to_number(nvl(
fcpoi.ALERT_LONG_RUNNING_TOLERANCE,0))))
and fcpoi.ALERT_LONG_RUNNING_ENABLED='Y'
and fcr.phase_code='R';
select threshold_value into v_spec_long_run_enabled
from fnd_oam_metval
where metric_short_name ='SPECIFIC_LONG_RUNNING_ENABLED';
select count(*) into v_spec_cnt
from fnd_conc_prog_onsite_info
where ALERT_LONG_RUNNING_ENABLED='Y' and rownum < 2;
select fcr.request_id request_id,
fcp.user_concurrent_program_name user_concurrent_program_name
from
fnd_concurrent_requests fcr,
fnd_conc_prog_onsite_info fcpoi,
fnd_concurrent_programs_vl fcp
where
fcr.program_application_id=fcpoi.program_application_id
AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and fcr.concurrent_program_id=fcpoi.concurrent_program_id
and ((sysdate -fcr.REQUESTED_START_DATE)*86400 >
(to_number(nvl(p_tol,0))*60))
and fcr.phase_code='P'
and fcr.status_code in ('I', 'Q');
select threshold_value,alert_enabled_flag
into v_lng_pend_req_count,v_lng_pend_req_alert_enable
from fnd_oam_metval
where metric_short_name ='LONG_PENDING_REQ_COUNT';
select threshold_value
into v_lng_pend_req_tolerance
from fnd_oam_metval
where metric_short_name ='LONG_PENDING_REQ_TOLERANCE';
select fcr.request_id request_id,
fcp.user_concurrent_program_name user_concurrent_program_name,
nvl(fcpoi.ALERT_LONG_PENDING_TOLERANCE,0)/60 tolerance_minutes
from
fnd_concurrent_requests fcr,
fnd_conc_prog_onsite_info fcpoi,
fnd_concurrent_programs_vl fcp
where
fcr.program_application_id=fcpoi.program_application_id
AND fcp.application_id = fcr.PROGRAM_APPLICATION_ID
AND fcp.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and fcr.concurrent_program_id=fcpoi.concurrent_program_id
and (sysdate -fcr.REQUESTED_START_DATE)*86400 >
(to_number(nvl(fcpoi.ALERT_LONG_PENDING_TOLERANCE,0)))
and fcpoi.ALERT_LONG_PENDING_ENABLED='Y'
and fcr.phase_code='P'
and fcr.status_code in ('I', 'Q');
select threshold_value
into v_spec_long_pend_enabled
from fnd_oam_metval
where metric_short_name ='SPECIFIC_LONG_PENDING_ENABLED';
select count(*) into v_spec_cnt
from fnd_conc_prog_onsite_info
where ALERT_LONG_PENDING_ENABLED='Y' and rownum < 2;