EBS常用SQL总结

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 where1=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' ANDROWNUM<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')

ANDUPPER(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')--根据请求组查找 ANDupper(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 WHERE1 = 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%'

GROUPBY t.user_concurrent_program_name ,e.executable_name ORDERBY 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 ,t.completion_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') ORDERBY 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 GROUPBY 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 表可以查看哪些类型的对象：

SELECTDISTINCTTYPEFROM ALL_SOURCE; TYPE PROCEDURE PACKAGE PACKAGE BODY LIBRARY TYPE BODY TRIGGER FUNCTION JAVA SOURCE TYPE 举例：查看程序包定义语句：

SELECT TEXT FROM ALL_SOURCE WHERETYPE = 'PACKAGE' ANDNAME = '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) GROUPBY u.name ,o.name ,o.ctime ,o.stime ,o.mtime; 输出：

@通过DBMS_METADATA包

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

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_metada.htm#ARPLS640

通过该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

WHERETYPE = 'PACKAGE BODY' AND text LIKE'%ERR_MESSAGE_054%' GROUPBY t.name;