Oracle EBS工具脚本

值集查询

---

SELECT ffs.flex_value_set_id,ffs.flex_value_set_name,ffv.flex_value,ffv.flex_value_meaning,ffv.description,ffv.flex_valueFROM applsys.fnd_flex_value_sets ffs, fnd_flex_values_vl ffvWHERE ffs.flex_value_set_id = ffv.flex_value_set_idAND ffs.flex_value_set_name = ?  --值集名称AND ffv.enabled_flag = 'Y'AND ffv.flex_value = ? --关联值AND SYSDATE BETWEEN nvl(ffv.start_date_active, SYSDATE) ANDnvl(ffv.end_date_active, SYSDATE + 1);

快码查询

---

SELECT flv.meaningFROM fnd_lookup_values flvWHERE flv.language = userenv('LANG')AND flv.lookup_type = ? --快码名称AND flv.enabled_flag = 'Y'AND flv.lookup_code = ?; --关联值

查询可执行请求

---

SELECT fcpv.user_concurrent_program_name 程序名称,fcpv.concurrent_program_name 并发程序简称,fcpv.output_file_type 输出格式,fefv.executable_name 执行程序简称,fefv.execution_file_name 执行文件名称,decode(fefv.execution_method_code,'I','PL/SQL存储过程','P','Oracle Reports') 执行方法FROM fnd_concurrent_programs_vl fcpv, fnd_executables_form_v fefvWHERE 1 = 1--AND USER_CONCURRENT_PROGRAM_NAME LIKE 'CUX%'AND fcpv.executable_id = fefv.executable_idAND fcpv.enabled_flag = 'Y'AND fcpv.concurrent_program_name = 'AUTOREMAPI' --并发简称--AND fcpv.user_concurrent_program_name = 'CUXAP_费用报销明细表(AP)' --请求名称--AND upper(fefv.execution_file_name) like upper('%cux_product_lean_cost_pkg.jb_mx_yf%') --注册并发名

批量取消请求

---

DECLARECURSOR c_r ISSELECT cp.user_concurrent_program_name,r.request_id,r.requested_by user_id,r.responsibility_id,r.responsibility_application_idFROM fnd_concurrent_requests r, fnd_concurrent_programs_vl cpWHERE r.concurrent_program_id = cp.concurrent_program_idAND r.phase_code IN ('P', 'R', 'C') --待定，运行中AND r.concurrent_program_id = 265358 --程序idAND r.request_date > trunc(SYSDATE);l_bool BOOLEAN;l_msg  VARCHAR2(2000);
BEGINFOR rec IN c_r LOOPfnd_global.apps_initialize(user_id      => rec.user_id, --请求提交用户IDresp_id      => rec.responsibility_id, --请求提交职责IDresp_appl_id => rec.responsibility_application_id); --职责应用l_bool := fnd_concurrent.cancel_request(request_id => rec.request_id,message    => l_msg);COMMIT;dbms_output.put_line(rec.user_concurrent_program_name || '=>请求ID:' ||rec.request_id || ',message:' || l_msg);END LOOP;END;

职责查询

-- 职责
SELECT distinct fst.responsibility_name     职责名FROM fnd_responsibility_tl fstWHERE 1 = 1AND fst.language = 'ZHS'AND fst.RESPONSIBILITY_NAME like '%%';-- 用户与职责
SELECT distinct wur.user_name 用户名,fst.responsibility_name     职责名FROM fnd_responsibility_tl fst,wf_all_user_roles wurWHERE 1 = 1AND fst.language = 'ZHS'-- AND fst.RESPONSIBILITY_NAME like '%%'-- 职责名字范围AND fst.RESPONSIBILITY_ID = wur.role_orig_system_id-- AND wur.user_name in ('','') -- 用户名范围order by wur.user_name;-- 查询请求在哪个职责下面
select fu.user_name,ppf.FULL_NAME,fr.RESPONSIBILITY_NAME,fur.START_DATE,fur.END_DATE,fcp.CONCURRENT_PROGRAM_NAME,fcp.USER_CONCURRENT_PROGRAM_NAMEfrom fnd_user                   fu,per_people_f               ppf,fnd_user_resp_groups_all   fur,fnd_responsibility_vl      fr,fnd_request_groups         frg,fnd_request_group_units    frgu,fnd_concurrent_programs_vl fcpwhere fu.user_id = fur.user_idand fu.employee_id = ppf.PERSON_IDand ppf.EFFECTIVE_END_DATE > sysdateand fur.RESPONSIBILITY_ID = fr.RESPONSIBILITY_IDand fr.REQUEST_GROUP_ID = frg.request_group_idand frgu.request_group_id = frg.request_group_idand frgu.request_unit_id = fcp.CONCURRENT_PROGRAM_ID--AND fcp.concurrent_program_id = 504369--and fu.user_name = '104267'order by 1, 2, 3, 6;

死锁处理

---

-- 死锁查询
SELECT l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,s.user#,l.os_user_name,s.machine,s.terminal,a.sql_text,a.actionFROM v$sqlarea a, v$session s, v$locked_object lWHERE l.session_id = s.sidAND s.prev_sql_addr = a.addressORDER BY sid, s.serial#;-- 死锁处理 杀掉serial#的请求
alter system kill session'135,397'-- 死锁处理
SELECT b.owner,b.object_name,c.logon_time 登录时间,c.client_identifier,-- '''' || c.sid || ',' || c.serial# || '''' 进程id, -- 会话标识符-会话序列号,c.username    数据库用户名称,c.command     正在处理的命令,c.status      会话当前状态,c.osuser      操作系统名称,c.machine     操作系统主机,c.terminal    操作系统终端,c.program     操作系统程序名称,c.type        会话类型,c.action      正在执行的操作名称,a.locked_mode,/*0：none1：null 空2：Row-S 行共享(RS)：共享表锁，sub share3：Row-X 行独占(RX)：用于行的修改，sub exclusive4：Share 共享锁(S)：阻止其他DML操作，share5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive6：exclusive 独占(X)：独立访问使用，exclusive*/'alter system kill session ' || '''' || c.sid || ',' || c.serial# ||''';' kill_commandFROM v$locked_object a, v$session c, dba_objects bWHERE c.sid = a.session_idAND b.object_id = a.object_idAND c.action <> '/'-- AND b.owner = 'WIP'AND c.logon_time < SYSDATE - 60 / 60 / 24AND c.action <> 'Concurrent Request'ORDER BY c.logon_time;

脚本获取包体

---

-- 获取包体
SELECT decode(ds.line, 1, 'CREATE OR REPLACE ' || ds.text, ds.text) textFROM sys.dba_source dsWHERE ds.name = ? --包名AND ds.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER')And ds.owner='APPS' --所在用户ORDER BY ds.line;

查询最后编译信息

---

SELECT t.last_ddl_time 最后编译时间, t.*FROM dba_objects tWHERE t.object_name = ?; --包名或视图名