ebs oracle pl sql开发_EBS 开发常用SQL

1.查看当前系统定义了那些值是中文的段值。

SELECT *

FROMfnd_flex_value_sets ffvsWHERE ffvs.flex_value_set_name LIKE 'CUX%'

AND EXISTS (SELECT 1

FROMfnd_flex_values_vl ffvWHERE ffvs.flex_value_set_id =ffv.flex_value_set_idAND (ascii(substr(ffv.flex_value

,1,1)) < 0 OR ascii(substr(ffv.flex_value

,1,1)) > 127)AND ffv.enabled_flag = 'Y');

2.查询当前系统程序包中引用内容

SELECT *

FROMall_source tWHERE t.type = 'PACKAGE BODY'

AND t.name LIKE 'XX%'

AND t.owner LIKE 'XX%'

AND t.text LIKE '%per_org_structure_versions%';

3.查询当前EBS的版本

SELECT *

FROM fnd_product_groups;

4.查看单取EBS数据库版本

SELECT *

FROM v$version;

5.查看所选表的字段信息

SELECT *

FROMall_tab_columnsWHERE table_name = 'PO_HEADERS_ALL';

6.查找当前应用服务器的网址

SELECT fnd_profile.value('APPS_WEB_AGENT')FROM dual;

7.查看表或者视图被某个对象引用

SELECT *

FROMall_dependencies depWHERE dep.referenced_name = 'MTL_SAFETY_STOCKS';

8.查看当前最耗时的进程

SELECT * FROM v$session_longops;

10.查看系统配置文件状态

SELECTfp.level_id

,fp.level_value

,fp.profile_option_valueFROMfnd_profile_options_vl fpo

,fnd_profile_option_values fpWHERE fpo.application_id =fp.application_idAND fpo.profile_option_id =fp.profile_option_idAND fpo.user_profile_option_name = 'FND: Debug Log Enabled';

11.查看当前系统的应用模块

SELECTfnd_application.application_short_name

,fnd_application.application_id

,fnd_product_installations.statusFROMfnd_application

,fnd_product_installationsWHERE fnd_application.application_id = fnd_product_installations.application_id;

12.利用应用简称或应用名称查找应用模块详细信息

SELECTfapp.application_short_name

,fapptl.application_name

,fapp.*

FROMfnd_application fapp

,fnd_application_tl fapptlWHERE fapptl.application_name = 'Purchasing' --fapp.application_short_name='XXSAT'

AND fapp.application_id =fapptl.application_idAND fapptl.language = 'US'

13.查看当前EBS系统注册的用户

SELECT *

FROMfnd_userWHERE user_name = upper('sysadmin');

14.查询消息的定义

SELECTfa.application_short_name

, fm.language_code

, fm.message_name

, fm.message_text

, fm.typeFROMfnd_new_messages fm

, fnd_application faWHERE fm.message_name = 'FWK_TBX_T_PAGE_GENERAL'

AND fm.application_id =fa.application_idAND fa.application_short_name = 'AK'

15.查看EBS系统当前的语言配置

SELECT userenv('language')FROM dual;

16.查看所有配置文件的名字

SELECT *

FROMfnd_profile_options_tl tWHERE t.profile_option_name = 'AFLOG_ENABLED';

17.查看配置文件的sql

SELECT t.user_profile_option_name "Profile Option"

,decode(a.level_id

,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') "Level"

,decode(a.level_id

,10001,'Site',10002,b.application_short_name

,10003,c.responsibility_key

,10004,d.user_name) "LevelValue"

,a.profile_option_value "Profile Value"FROMfnd_profile_option_values a

,fnd_application b

,fnd_responsibility c

,fnd_user d

,fnd_profile_options e

,fnd_profile_options_tl tWHERE a.profile_option_id =e.profile_option_idAND e.profile_option_name = 'RCV_TP_MODE'

AND a.level_value = b.application_id(+)AND a.level_value = c.responsibility_id(+)AND a.level_value = d.user_id(+)AND t.profile_option_name =e.profile_option_nameAND t.language = 'US'

ORDER BYe.profile_option_name

,a.level_idDESC;

15.时间转换为数字

SELECT to_number(substr(to_char(to_date('12:56:23','hh24:mi:SS')

,'hh24miss')

,1,2)) * 3600 + to_number(substr(to_char(to_date('12:56:23','hh24:mi:SS')

,'hh24miss')

,3,2)) * 60 + to_number(substr(to_char(to_date('12:56:23','hh24:mi:SS')

,'hh24miss')

,5,2))FROM dual;

16.数字转换为时间

SELECT to_char(trunc(46583 / 3600)) || ':' || to_char(trunc(MOD(46583,3600) / 60)) || ':' ||to_char(trunc(MOD(MOD(46583,3600)

,60)))FROM dual;

17.获得当前会话编号

SELECT userenv('SESSIONID')FROM dual;

18.查看定义的并发程序挂在那个职责

SELECTresponsibility_name

,frg.request_group_name

,fcpv.concurrent_program_name

,fcpv.user_concurrent_program_name

,fcpv.descriptionFROMfnd_request_groups frg

,fnd_request_group_units frgu

,fnd_concurrent_programs_vl fcpv

,fnd_responsibility_vl frvWHERE frgu.request_unit_type = 'P'

AND frgu.request_group_id =frg.request_group_idAND frgu.request_unit_id =fcpv.concurrent_program_idAND frv.request_group_id(+) =frg.request_group_idAND fcpv.user_concurrent_program_name = < 并发程序全称，例： 'CUX:接收入库单' >

ORDER BY responsibility_name

19.查看所查内容被那些包引用

SELECT *

FROMall_source tWHERE t.text LIKE '%IBY_DISBURSEMENT_COMP_PUB%';

20.查看当前EBS数据库对应字符集

SELECT *

FROM nls_database_parameters;

21.查看数据库用户密码

略

22.查找功能所属职责

SELECTfffv.function_name

,fm.menu_name

,fr.responsibility_keyFROMfnd_form_functions_vl fffv

,fnd_menu_entries_vl fmev

,fnd_menus fm

,fnd_responsibility frWHERE fffv.function_id =fmev.function_idAND fmev.menu_id =fm.menu_idAND fmev.menu_id = fr.menu_id(+)AND fffv.function_name = '&function_name';

23.查看配置文件配置

SELECTpro.profile_option_name

,pro.user_profile_option_name

,lev.level_type TYPE

,lev.level_code

,lev.level_name

,prv.profile_option_valueFROMapps.fnd_profile_options_vl pro

,applsys.fnd_profile_option_values prv

,(SELECT 10001level_id

,'Site'level_type

,0level_value

,'Site'level_code

,'Site'level_nameFROMdualUNION ALL

SELECT 10002level_id

,'App'level_type

,app.application_id level_value

,app.application_short_name level_code

,app.application_name level_nameFROMapps.fnd_application_vl appUNION ALL

SELECT 10003level_id

,'Resp'level_type

,resp.responsibility_id level_value

,resp.responsibility_key level_code

,resp.responsibility_name level_nameFROMapps.fnd_responsibility_vl respUNION ALL

SELECT 10004level_id

,'User'level_type

,usr.user_idlevel_value

,usr.user_namelevel_code

,usr.user_namelevel_nameFROMapplsys.fnd_user usr) levWHERE pro.profile_option_id = prv.profile_option_id(+)AND prv.level_id = lev.level_id(+)AND prv.level_value = lev.level_value(+)AND pro.user_profile_option_name LIKE 'CUX%' --Profile名称

ORDER BYpro.profile_option_name

,lev.level_type

,lev.level_name;

24.查看Oracle Patch版本

SELECTdd.patch_name

,pp.creation_date

,pp.driver_file_name

,lang.languageFROMad_patch_drivers pp

,ad_applied_patches dd

,ad_patch_driver_langs langWHERE pp.applied_patch_id =dd.applied_patch_idAND lang.patch_driver_id =pp.patch_driver_idAND lang.language = userenv('LANG')ORDER BY pp.creation_date;

25.根据描述性弹性域的标题查找描述性弹性域表和列

SELECTfnd_dfv.title

,fnd_dfv.descriptive_flexfield_name

,fnd_dfv.application_table_name

,fnd_dfu.application_column_name

,fnd_dfu.form_left_prompt

,fnd_dfu.form_above_promptFROMfnd_descriptive_flexs_vl fnd_dfv

,fnd_descr_flex_col_usage_vl fnd_dfuWHERE fnd_dfv.title = '物料' --如：物料

AND fnd_dfu.descriptive_flexfield_name = fnd_dfv.descriptive_flexfield_name;

26.查看当前环境登录的用户

SELECT u.user_name,app.application_short_name

,fat.application_name

,fr.responsibility_key

,frt.responsibility_name

,fff.function_name

,fft.user_function_name

,icx.function_type

,icx.first_connect

,icx.last_connectFROMicx_sessions icx

,fnd_user u

,fnd_application app

,fnd_application_tl fat

,fnd_responsibility fr

,fnd_responsibility_tl frt

,fnd_form_functions fff

,fnd_form_functions_tl fftWHERE 1 = 1

AND u.user_id = icx.user_id

AND icx.responsibility_application_id =app.application_idAND fat.application_id =icx.responsibility_application_idAND fat.language = 'ZHS'

AND fr.application_id =icx.responsibility_application_idAND fr.responsibility_id =icx.responsibility_idAND frt.language = 'ZHS'

AND frt.application_id =icx.responsibility_application_idAND frt.responsibility_id =icx.responsibility_idAND fff.function_id =icx.function_idAND fft.function_id =icx.function_idAND icx.disabled_flag != 'Y'

AND icx.pseudo_flag = 'N'

AND (icx.last_connect + decode(fnd_profile.value('ICX_SESSION_TIMEOUT')

,NULL,icx.limit_time

,0,icx.limit_time

,fnd_profile.value('ICX_SESSION_TIMEOUT') / 60) / 24) >SYSDATEAND icx.counter < icx.limit_connects;

27.查看当前系统所有存在个性化的功能

SELECT DISTINCTfun.user_function_name

,fun.function_nameFROMfnd_form_custom_rules fcr

,fnd_form_functions_vl funWHERE fun.function_name =fcr.function_nameORDER BY fun.function_name;

28.查看当前请求挂在那些请求组中

SELECTrg.request_group_name

,fcp.concurrent_program_name

,fcp.user_concurrent_program_name

,rg.application_id

,rg.request_group_idFROMfnd_request_groups rg

,fnd_request_group_units rgu

,fnd_concurrent_programs_vl fcpWHERE rg.request_group_id =rgu.request_group_idAND rgu.request_unit_id =fcp.concurrent_program_idAND fcp.user_concurrent_program_name = 'CUX:库存帐龄表';

29.查看配置文件的配置情况

SELECTt.user_profile_option_name us_name

,l.user_profile_option_name zhs_name

,decode(v.level_id

,10001,'地点层',10002,'应用层',10003,'职责层',10004,'用户层',10005,'服务器层',10006,'组织层','其它') level_name

,decode(v.level_id

,10002,(SELECTapplication_nameFROMapps.fnd_application_vlWHERE application_id =v.level_value)

,10003,(SELECTresponsibility_nameFROMapps.fnd_responsibility_vlWHERE responsibility_id =v.level_valueAND application_id =v.level_value_application_id)

,10004,(SELECT user_name

FROMapps.fnd_userWHERE user_id =v.level_value)

,10005,(SELECTnode_nameFROMapps.fnd_nodesWHERE node_id =v.level_value)

,10006,(SELECTNAMEFROMapps.hr_operating_unitsWHERE organization_id =v.level_value)

,v.level_value) level_value

,v.profile_option_value

,u.user_name,v.last_update_dateFROMapps.fnd_profile_options_vl t

,apps.fnd_profile_options_tl l

,apps.fnd_profile_option_values v

,apps.fnd_user uWHERE t.application_id =v.application_idAND t.profile_option_id =v.profile_option_idAND t.profile_option_name =l.profile_option_nameAND l.language = 'ZHS'

AND u.user_id =v.last_updated_byAND t.start_date_active <=SYSDATEANDnvl(t.end_date_active

,SYSDATE)>=SYSDATEAND user_id NOT IN (-1,0,1,2,3,4,5,6,7)AND l.user_profile_option_name = 'MO：安全性配置文件'

ORDER BY t.user_profile_option_name;

30.报表trace定位

SELECT 'Request id:' ||request_id

,'Trace id:' ||oracle_process_id

,'Trace Flag:' ||req.enable_trace

,'Trace Name:' || dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc','Prog. Name:' ||prog.user_concurrent_program_name

,'File Name:' || execname.execution_file_name ||execname.subroutine_name

,'Status :' ||decode(phase_code

,'R','Running') || '-' ||decode(status_code

,'R','Normal')

,'SID Serial:' || ses.sid || ',' ||ses.serial#

,'Module :' ||ses.moduleFROMfnd_concurrent_requests req

,v$session ses

,v$processproc,v$parameter dest

,v$parameter dbnm

,fnd_concurrent_programs_vl prog

,fnd_executables execnameWHERE req.request_id = &request_idAND req.oracle_process_id = proc.spid(+)AND proc.addr = ses.paddr(+)AND dest.name = 'user_dump_dest'

AND dbnm.name = 'db_name'

AND req.concurrent_program_id =prog.concurrent_program_idAND req.program_application_id =prog.application_idAND prog.application_id =execname.application_idAND prog.executable_id = execname.executable_id;

31.停止正在运行的请求

SELECTfcr.rowid

,fcr.*

FROMfnd_concurrent_requests fcrWHERE 1 = 1

AND fcr.phase_code IN ( /*'P',*/'R')AND fcr.request_id = 558746; --phase_code,status_code = C

32.查看当前EBS环境的服务节点部署情况

SELECTNAME,

server_typeFROMfnd_app_servers,

fnd_nodesWHERE fnd_app_servers.node_id =fnd_nodes.node_idAND server_type IN ('APPS','DB');