EBS技术笔记

1.配置文件定义

fnd_profile.value('CUX_BARCODE_PATH')

2.测试请求方法：

dbms_lock.sleep(seconds => );

3.相关用法
1)greatest
2)%ROWTYPE
3)add_months
4)call fnd_global.APPS_INITIALIZE( 1318 , 50583 , 401 )
5)fnd_profile.VALUE( 'ORG_ID' )
6)nvl2   NVL2(表达式,不为空设值,为空设值)。
7)fnd_date.canonical_to_date
就是将一个标准日期格式的字符串型变量转换为标准的PL/SQL的日期格式。
8)Select * from table(dbms_xplan.display_awr(‘&sqlid’,null))
9)比较大小函数 sign
函数语法：sign(n)
函数说明：取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
10)异常信息
v_backtrace := dbms_utility.format_error_backtrace;
sqlcode函数用来返回pl/sql执行内部异常代码。
sqlerrm函数返回指定错误代码的错误信息。
11)ceil
12)EBS 版本查看
select RELEASE_NAME from apps.fnd_product_groups

4.查找数据库对象引用情况

SELECT *
  FROM dba_source ds
 WHERE UPPER (ds.text) LIKE '%CUX_WZC_ACCOUNT_RENT%';
Select * From Dba_Dependencies t Where t.referenced_name='CUX_ITEM_TAG_MES' 
select dm.owner,dm.mview_name,dm.master_link from dba_mviews dm where dm.owner='HMCS'
select * from dba_db_links;

sys:

select status from all_objects where
dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy'

5.PG/RN编译

cd $OA_HTML/WEB-INF/classes
java oracle.jrad.tools.xml.importer.XMLImporter $OA_HTML/WEB-INF/classes/test/oracle/apps/cux/pos/fd/webui/PurchaseOrdersPG.xml -jdk13 -mmddir $OA_HTML/jrad -username apps -password apps -rootdir $OA_HTML/WEB-INF/classes -validate -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.16.18.158)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=ERP)(INSTANCE_NAME=ERP1)))"

6.form编译
执行如下Shell命令，编译发布fmb，请注意替换过Apps密码信息
cd $AU_TOP/forms/ZHS
frmcmp_batch $CUX_TOP/forms/ZHS/fmb/CUXRUNOAF userid=apps/密码 output_file=$CUX_TOP/forms/US/CUXRUNOAF

7.片段
1)FOR UPDATE OFheader_id NOWAIT;
2)fnd_conc_date.string_to_date
3）select * from v$parameter where name like '%dump%'

8.报表trace
以sysadmin登录， 职责 system administrator -> Concurrent -> Program -> Define -> F11 ->
在Program栏位输入报表名称如 HW AR Accounts Receivable Balance Report -> CTRL+F11 -> 查询出来后点击左下方的check box : Enable Trace -> 点击菜单下的保存图标 -> 退出后以选择合适的职责提交一个request，报表名选择以上修改的报表，运行后查看数据库user_dump_dest[SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest';]对应的目录下的含有此request_id 的 trc 文件，并使用如下格式化命令进行转化：
tkprof  xxx.trc  xxx.txt  explain=apps/apps_passwd  sort='(prsela,exeela,fchela)'

9.通过sql_id查sql

select nvl(sql_text, to_clob('** SQL Text Not Available **')) as sql_text
  from dba_hist_sqltext a, dba_hist_sqlstat b
 where a.sql_id = b.sql_id
   and b.sql_id = '&sql_id';
select module,sql_id from dba_hist_sqlstat where sql_id='9jhxjr42pr3su'; 
--看SQL执行参数
select sql_exec_id from dba_hist_active_sess_history where sql_id='&sql_id'
SELECT dbms_sqltune.report_sql_monitor(sql_id => '&sql_id', sql_exec_id => '&sql_exec_id') FROM dual; 
select * from gv$sqlarea t where t.SQL_ID = '8avjyjr38uzxz'; 
select * from gv$sql t where t.SQL_ID='8avjyjr38uzxz'
select * from gv$sqlarea t where t.SQL_ID = '2sp4b6r61wccw';

10.根据机器名查sql_id

select s.PREV_SQL_ID,s.SQL_ID from gv$session s where s.MACHINE like '%ZHANG%' and s.STATUS='ACTIVE';

11.查找有效会话数

select count(*) from gv$session t where t.status='ACTIVE'