EBS常用SQL总结.docx

Session相关
1）查看session状态
Pl/Sql developer->tools->sessions

2）查看那些表被哪个session锁定
SELECT a.object_name,
a.object_type,
v.session_id
FROM all_objects a,
v$locked_object v
WHERE v.object_id = a.object_id;

查找配置文件
select t.PROFILE_OPTION_NAME,a.application_name ,t.USER_PROFILE_OPTION_NAME,T.DESCRIPTION
from FND_PROFILE_OPTIONS_VL t, fnd_application_tl a
where 1=1
--AND t.PROFILE_OPTION_NAME like '%ZZOM207%'
and t.APPLICATION_ID=a.application_id
and a.language='ZHS';

请求相关
1）查找所有请求对应的职责
SELECT FRTL.RESPONSIBILITY_NAME,
PRO.USER_CONCURRENT_PROGRAM_NAME,
PROS.CONCURRENT_PROGRAM_NAME
FROM FND_RESPONSIBILITY_TL FRTL,
FND_RESPONSIBILITY FR,
FND_REQUEST_GROUP_UNITS REQ,
FND_CONCURRENT_PROGRAMS_TL PRO,
FND_CONCURRENT_PROGRAMS PROS
WHERE FRTL.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FRTL.APPLICATION_ID = FR.APPLICATION_ID
AND FRTL.LANGUAGE = 'ZHS'
AND FR.REQUEST_GROUP_ID = REQ.REQUEST_GROUP_ID
AND REQ.REQUEST_UNIT_ID = PRO.CONCURRENT_PROGRAM_ID
AND PRO.CONCURRENT_PROGRAM_ID = PROS.CONCURRENT_PROGRAM_ID
AND PRO.USER_CONCURRENT_PROGRAM_NAME LIKE '%IES 总括请求书传送至海外%'

2）查找已提交请求相关信息
SELECT T.REQUEST_ID,
T.REQUEST_DATE,
T.REQUESTED_BY,
FRTL.RESPONSIBILITY_NAME,
t.outfile_name,
pro.user_concurrent_program_name,
pros.concurrent_program_name
FROM FND_CONCURRENT_REQUESTS T,
FND_RESPONSIBILITY_TL FRTL,
FND_CONCURRENT_PROGRAMS_TL PRO,
fnd_concurrent_programs pros
WHERE T.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID
AND T.CONCURRENT_PROGRAM_ID = PRO.CONCURRENT_PROGRAM_ID
AND pro.concurrent_program_id=pros.concurrent_program_id
AND pro.language='ZHS'
--AND t.request_id=3897869
--AND PRO.USER_CONCURRENT_PROGRAM_NAME LIKE '%STD 供应商帐龄报表%'
AND pros.concurrent_program_name='ZZAPGML010C'
AND ROWNUM < 10

SELECT t.request_id "请求ID"
,t.request_date "请求时间"
,frtl.responsibility_name "职责"
,t.outfile_name "输出文件路径"
,pro.user_concurrent_program_name "报表名"
,pros.concurrent_program_name "可执行名"
FROM fnd_concurrent_requests t
,fnd_responsibility_tl frtl
,fnd_concurrent_programs_tl pro
,fnd_concurrent_programs pros
WHERE t.responsibility_id = frtl.responsibility_id
AND t.concurrent_program_id = pro.concurrent_program_id
AND pro.concurrent_program_id = pros.concurrent_program_id
AND pro.language = 'ZHS'
AND frtl.language='ZHS'
AND t.request_id = 3897869 --根据请求ID
--AND pro.user_concurrent_program_name LIKE '%STD 供应商帐龄报表%' --根据报表名称
--AND pros.concurrent_program_name = 'ZZAPGML010C' --根据可执行
;

3）查找未提交请求相关信息
SELECT APP.APPLICATION_ID, APP.APPLICATION_NAME
FROM FND_APPLICATION_TL APP
WHERE APP.LANGUAGE = 'ZHS'
AND APP.APPLICATION_NAME LIKE '%应收帐款管理系统%';

SELECT G.REQUEST_GROUP_NAME "请求组",
APG.APPLICATION_NAME "请求组的应用",
T.USER_CONCURRENT_PROGRAM_NAME "程序",
APP.APPLICATION_NAME "程序的应用",
P.CONCURRENT_PROGRAM_NAME "可执行"
FROM FND_REQUEST_GROUP_UNITS U,
FND_CONCURRENT_PROGRAMS_TL T,
FND_CONCURRENT_PROGRAMS P,
FND_REQUEST_GROUPS G,
FND_APPLICATION_TL APP,
FND_APPLICATION_TL APG
WHERE U.REQUEST_UNIT_ID = T.CONCURRENT_PROGRAM_ID
AND G.REQUEST_GROUP_ID = U.REQUEST_GROUP_ID
AND T.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
AND T.LANGUAGE = 'ZHS'
AND APP.LANGUAGE = 'ZHS'
AND APG.LANGUAGE = 'ZHS'
AND U.APPLICATION_ID = APG.APPLICATION_ID
AND T.APPLICATION_ID = APP.APPLICATION_ID
AND G.APPLICATION_ID = APG.APPLICATION_ID
--AND UPPER(G.REQUEST_GROUP_NAME) = UPPER('STD AR User Programs')
AND UPPER(T.USER_CONCURRENT_PROGRAM_NAME) LIKE '%HCST:ACTUAL WIP VALUES REPORT';
--请求组的应用和可执行程序的应用可以不一样

SELECT g.request_group_name "请求组"
,t.user_concurrent_program_name "报表"
,p.concurrent_program_name "可执行"
,rt.responsibility_name "职责"
FROM fnd_request_group_units u
,fnd_concurrent_programs_tl t
,fnd_concurrent_programs p
,fnd_request_groups g
,fnd_responsibility_vl r
,apps.fnd_responsibility_tl rt
WHERE u.request_unit_id = t.concurrent_program_id
AND g.request_group_id = u.request_group_id
AND t.concurrent_program_id = p.concurrent_program_id
AND r.request_group_id = u.request_group_id
AND rt.responsibility_id = r.responsibility_id
AND t.language = 'ZHS'--中文环境
AND rt.language = 'ZHS'----中文环境
--AND UPPER(G.REQUEST_GROUP_NAME) = UPPER('STD AR User Programs')--根据请求组查找
AND upper(t.user_concurrent_program_name) LIKE '%资金出纳%' --根据报表名查找
--AND p.concurrent_program_name LIKE '%ZZGLGDL029C%' --根据程序名查找
--AND rt.responsibility_name LIKE '%QD1%' --根据职责查找
;

4）查找职责对应的菜单和请求组
SELECT RT.RESPONSIBILITY_NAME, G.REQUEST_GROUP_NAME, MV.MENU_NAME
FROM FND_RESPONSIBILITY_VL RV,
FND_RESPONSIBILITY_TL RT,
FND_REQUEST_GROUPS G,
FND_MENUS_VL MV
WHERE RV.REQUEST_GROUP_ID = G.REQUEST_GROUP_ID
AND RT.RESPONSIBILITY_ID = RV.RESPONSIBILITY_ID
AND RV.MENU_ID = MV.MENU_ID
AND RT.LANGUAGE = 'ZHS'
AND RT.RESPONSIBILITY_NAME LIKE '%BJ1 应收帐款用户%';

查找可执行
SELECT t.user_concurrent_program_name
,e.executable_name
FROM fnd_concurrent_programs_tl t
,fnd_concurrent_programs p
,fnd_executables_vl e
WHERE 1 = 1
AND t.concurrent_program_id = p.concurrent_program_id
AND p.executable_id = e.executable_id
AND t.language = 'ZHS'
AND e.executable_name LIKE 'ZZ%'
GROUP BY t.user_concurrent_program_name
,e.executable_name
ORDER BY e.executable_name;

SELECT *
FROM sys.all_source t
WHERE T.TYPE IN('PACKAGE','PACKAGE BODY')
and T.name LIKE 'ZZ%PKG';

SELECT T.name,PT.USER_CONCURRENT_PROGRAM_NAME,T.TYPE,T.line,T.TEXT
FROM sys.all_source t
,fnd_executables_vl e
,fnd_concurrent_programs_tl PT
,fnd_concurrent_programs p
WHERE t.type IN ('PACKAGE', 'PACKAGE BODY')
AND t.name LIKE 'ZZCSTDBT003C%'
AND E.EXECUTABLE_NAME=T.name
AND E.EXECUTABLE_ID=P.EXECUTABLE_ID
AND P.CONCURRENT_PROGRAM_ID=PT.CONCURRENT_PROGRAM_ID;

SELECT t.request_id
,pletion_text
,r.responsibility_name
,p.concurrent_program_name
,pt.user_concurrent_program_name
,t.actual_start_date
,t.actual_completion_date
FROM apps.fnd_concurrent_requests t
,apps.fnd_concurrent_programs p
,apps.fnd_concurrent_programs_tl pt
,apps.fnd_responsibility_tl r
WHERE t.concurrent_program_id = p.concurrent_program_id
AND pt.concurrent_program_id = p.concurrent_program_id
AND pt.language = 'ZHS'
AND t.responsibility_id = r.responsibility_id
AND r.language = 'ZHS'
--AND pt.user_concurrent_program_name LIKE '%资产台帐%'
--AND P.CONCURRENT_PROGRAM_NAME LIKE '%ZZARRZZ104C%'
--AND r.responsibility_name LIKE '%%'
AND t.request_date BETWEEN to_date('2011-08-01', 'yyyy-mm-dd') AND
to_date('2011-09-10', 'yyyy-mm-dd')
ORDER BY t.request_date;

;

Oracle对象
1）查找无效对象
--查找有效/无效对象
SELECT T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE, T.STATUS
FROM ALL_OBJECTS T
WHERE T.OBJECT_NAME LIKE '%FND_LDAP_WRAPPER%'
UNION
SELECT T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE, T.STATUS
FROM ALL_OBJECTS t
WHERE t.STATUS = 'INVALID'
AND t.OBJECT_NAME LIKE '%ZZPAC013%';

2）查找表在哪些地方用到
先来确认下，通过ALL_DEPENDENCIES 表可以查看哪些类型的对象
SELECT t.TYPE FROM All_Dependencies t GROUP BY t.TYPE;
TYPE
FUNCTION
INDEX
JAVA CLASS
MATERIALIZED VIEW
PACKAGE
PACKAGE BODY
PROCEDURE
TABLE
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW

使用举例：查找哪些地方用到GL_CODE_COMBINATIONS表
SELECT *
FROM ALL_DEPENDENCIES T
WHERE T.REFERENCED_NAME = 'GL_CODE_COMBINATIONS';

3）查找数据库中对象定义语句
有时候，我们想查看表，存储，触发器等对象的定语语句，有以下两种方法：
=>查 all_source 表
=>用DBMS_METADATA 包。

@通过all_source 表
先来确认下，通过all_source 表可以查看哪些类型的对象：
SELECT DISTINCT TYPE FROM ALL_SOURCE;
TYPE
PROCEDURE
PACKAGE
PACKAGE BODY
LIBRARY
TYPE BODY
TRIGGER
FUNCTION
JAVA SOURCE
TYPE

举例：查看程序包定义语句：
SELECT TEXT
FROM ALL_SOURCE
WHERE TYPE = 'PACKAGE'
AND NAME = 'CUX_TEST';

查看程序包修改时间
SELECT u.name
,o.name
,o.ctime
,o.stime
,o.mtime
FROM sys.obj$ o
,sys.source$ s
,sys.user$ u
WHERE o.obj# = s.obj#
AND o.owner# = u.user#
AND o.type# IN (7, 8, 9, 11, 12, 13, 14)
AND o.name LIKE 'ZZ%'
AND (o.mtime > SYSDATE - 1
or o.stime > SYSDATE - 1
or o.ctime > SYSDATE - 1)
GROUP BY u.name
,o.name
,o.ctime
,o.stime
,o.mtime;
输出：

@通过DBMS_METADATA包
Oracle 的在线文档，对这个包有详细说明：
DBMS_METADATA

通过该dbms_metadata 包的get_ddl（） 方法，我们可以查看表，索引，视图，存储过程等的定义语句。
用法
select dbms_metadata.get_ddl('对象类型','名称','用户名') from dual;
举例：
SET LONG 9999;
select dbms_metadata.get_ddl('TABLE','ZZ_CONST_MST','ZZ') from dual;
输出：

PKG

查看程序包体
SELECT t.name
FROM all_source t
WHERE TYPE = 'PACKAGE BODY'
AND text LIKE '%ERR_MESSAGE_054%'
GROUP BY t.name;