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 ,ER_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 nguage='ZHS';

请求相关

1）查找所有请求对应的职责

SELECT FRTL.RESPONSIBILITY_NAME,

ER_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 NGUAGE = '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 ER_CONCURRENT_PROGRAM_NAME LIKE'%IES 总括请求书传送至海外%'

2）查找已提交请求相关信息

SELECT T.REQUEST_ID,

T.REQUEST_DATE,

T.REQUESTED_BY,

FRTL.RESPONSIBILITY_NAME,

t.outfile_name,

er_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 nguage='ZHS'

--AND t.request_id=3897869

--AND ER_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 "输出文件路径"

,er_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 nguage = 'ZHS'

AND nguage='ZHS'

AND t.request_id = 3897869--根据请求ID

--AND er_concurrent_program_name LIKE '%STD 供应商帐龄报表%' --根据报表名称--AND pros.concurrent_program_name = 'ZZAPGML010C' --根据可执行

;