编者按：
当Oracle数据库面临存量市场的时候，对Oracle数据库强烈依赖的应用程序会变成吸收大量Oracle数据库从业者的阵地，比如Oracle ERP。
【免责声明】本公众号文章仅代表个人观点，与任何公司无关，仅供参考。
编辑|SQL和数据库技术(ID:SQLplusDB)
查询父和子并发请求的运行详细SQL
SELECTfcr.request_id "Child Request ID",parent_request_id "Parent Request ID",fcptl.user_concurrent_program_name "Parent Program Name",fcr.phase_code,fcr.status_code,to_char(fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Time",round((fcr.actual_completion_date - fcr.actual_start_date) * 1440, 2) "Elapsed Mins"FROM(SELECTfcr1.request_idFROMapps.fnd_concurrent_requests fcr1WHERE1 = 1START WITHfcr1.request_id = &parent_request_idCONNECT BYPRIOR fcr1.request_id = fcr1.parent_request_id) x,apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs fcp,apps.fnd_concurrent_programs_tl fcptlWHEREfcr.request_id = x.request_idAND fcr.concurrent_program_id = fcp.concurrent_program_idAND fcr.program_application_id = fcp.application_idAND fcp.application_id = fcptl.application_idAND fcp.concurrent_program_id = fcptl.concurrent_program_idAND fcptl.language = 'US'ORDER BY1;
2. 查询Scheduled的并发请求列表SQL
SELECTfcr.request_id,fcpt.user_concurrent_program_name|| nvl2(fcr.description, ' ('|| fcr.description|| ')', NULL) conc_prog,fu.user_name requestor,fu.description requested_by,fu.email_address,frt.responsibility_name requested_by_resp,TRIM(fl.meaning) status,fcr.phase_code,fcr.status_code,fcr.argument_text "PARAMETERS",to_char(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start,to_char((fcr.requested_start_date), 'HH24:MI:SS') start_time,decode(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold,CASEWHEN fcr.hold_flag = 'Y' THENsubstr(fu.description, 0, 40)END last_update_by,CASEWHEN fcr.hold_flag = 'Y' THENfcr.last_update_dateEND last_update_date,fcr.increment_dates,CASEWHEN fcrc.class_info IS NULL THEN'Yes: '|| to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')ELSE'n/a'END run_once,CASEWHEN fcrc.class_type = 'P' THEN'Repeat every '|| substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1)|| decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months','H', ' hours', 'D', ' days')|| decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C',' from the completion of the prior run')ELSE'n/a'END set_days_of_week,CASEWHEN fcrc.class_type = 'S'AND instr(substr(fcrc.class_info, 33), '1', 1) > 0 THEN'Days of week: '|| decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ')|| decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ')|| decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ')|| decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ')|| decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ')|| decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ')|| decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')ELSE'n/a'END days_of_weekFROMapps.fnd_concurrent_requests fcr,apps.fnd_user fu,apps.fnd_concurrent_programs fcp,apps.fnd_concurrent_programs_tl fcpt,apps.fnd_printer_styles_tl fpst,apps.fnd_conc_release_classes fcrc,apps.fnd_responsibility_tl frt,apps.fnd_lookups flWHEREfcp.application_id = fcpt.application_idAND fcr.requested_by = fu.user_idAND fcr.concurrent_program_id = fcp.concurrent_program_idAND fcr.program_application_id = fcp.application_idAND fcr.concurrent_program_id = fcpt.concurrent_program_idAND fcr.responsibility_id = frt.responsibility_idAND fcr.print_style = fpst.printer_style_name (+)AND fcr.release_class_id = fcrc.release_class_id (+)AND fcr.status_code = fl.lookup_codeAND fl.lookup_type = 'CP_STATUS_CODE'AND fcr.phase_code = 'P'AND frt.language = 'US'AND fpst.language = 'US'AND fcpt.language = 'US'ORDER BYfu.description,fcr.requested_start_date ASC
3.查询某并发请求相关信息的SQL
SELECTfcrs.request_id,fcrs.user_concurrent_program_name,fcrs.actual_start_date,fcrs.actual_completion_date,floor(((fcrs.actual_completion_date - fcrs.actual_start_date) * 24 * 60 * 60) 3600)|| ':'|| floor((((fcrs.actual_completion_date - fcrs.actual_start_date) * 24 * 60 * 60) - floor(((fcrs.actual_completion_date - fcrs.actual_start_date) *24 * 60 * 60) 3600) * 3600) / 60)|| ':'|| round((((fcrs.actual_completion_date - fcrs.actual_start_date) * 24 * 60 * 60) - floor(((fcrs.actual_completion_date - fcrs.actual_start_date) *24 * 60 * 60) / 3600) * 3600 -(floor((((fcrs.actual_completion_date - fcrs.actual_start_date) * 24 * 60 * 60) - floor(((fcrs.actual_completion_date -fcrs.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) "HOURS:MINUTES:SECONDS",fcrs.argument_text,fcrs.requestor,decode(fcrs.status_code, 'A', 'Waiting', 'B', 'Resuming','C', 'Normal', 'D', 'Cancelled', 'E','Errored', 'F', 'Scheduled', 'G', 'Warning','H', 'On Hold', 'I', 'Normal', 'M','No Manager', 'Q', 'Standby', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating', 'U','Disabled', 'W', 'Paused', 'X', 'Terminated','Z', 'Waiting', fcrs.status_code) "Status",decode(fcrs.phase_code, 'C', 'Completed', 'I', 'Inactive','R', 'Running', 'A', 'Active', fcrs.phase_code) "Phase Code",fcrs.completion_text,fcrs.responsibility_application_id,frt.responsibility_name,fcrs.save_output_flag,fcrs.request_date,decode(fcrs.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus','H', 'Host', 'L', 'SQL*Loader', 'A','Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports','S', 'Immediate', fcrs.execution_method_code) execution_method,fcrs.concurrent_program_id,fcrs.program_short_name,fcrs.printer,fcrs.parent_request_idFROMfnd_conc_req_summary_v fcrs,fnd_responsibility_tl frtWHERE1 = 1AND user_concurrent_program_name LIKE '%'--and argument_text LIKE '%'--and requestor not in ('SYSADMIN','INVADMIN')--and request_id = 9686914AND frt.language = 'US'AND fcrs.responsibility_id = frt.responsibility_id--and fcrs.actual_start_date < sysdate--and fcrs.phase_code = 'R'--and fcrs.status_code = 'X'--and fcrs.status_code not in ('P','D','Q','C')--and trunc(fcrs.actual_start_date) =trunc(sysdate)--and trunc(fcrs.actual_completion_date) = trunc(sysdate)ORDER BYfcrs.actual_start_date DESC;
文章转载自SQL和数据库技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
