EBS常用SQL总结.docx

Session相关1）查看session状态Pl/Sql developer-tools-sessions2）查看那些表被哪个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 查 all_source 表=用DBMS_METADATA 包。 通过all_source 表先来确认下，通过all_source 表可以查看哪些类型的对象：SELECT DISTINCT TYPE FROM ALL_SOURCE;TYPEPROCEDUREPACKAGEPACKAGE BODYLIBRARYTYPE BODYTRIGGERFUNCTIONJAVA SOURCETYPE举例：查看程序包定义语句：SELECT TEXT FROM ALL_SOURCE WHERE TYPE = PACKAGE AND NAME = CUX_TEST;查看程序包修改时间SELECT , ,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 LIKE ZZ% AND (o.mtime SYSDATE - 1 or o.stime SYSDATE - 1 or o.ctime SYSDATE - 1) GROUP BY , ,o.ctime ,o.stime ,o.mtime;输出：通过DBMS_METADATA包Oracle 的在线文档，对这个包有详细说明：DBMS_METADATA/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_M