《Oracle Applications DBA 基础》 - 11 - 12 FNDCPASS ，FND_STATS 和 FNDLOAD

《Oracle Applications DBA 基础》 - 11 - 12 FNDCPASS ，FND_STATS 和 FNDLOAD
==================================================

11 和 12 节 介绍 Oracle Applications 里几种常用的工具， FNDCPASS， FND_STATS,
FND_LOAD.


FNDCPASS 是用来修改fnd_user和fnd_oracle_userid上面用户的密码的。
### 例子 1: 修改 apps 用户的password

shutdown apps tier，

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS password

然后 运行 AUTOCONFIG， 再start app tier 。

### 例子 2: 修改 sysadmin  用户的password
不用 shutdown app tier，

FNDCPASS apps/password 0 Y system/manager USER SYSADMIN password

然后 restart apache, forms, reports, 806 listener, and cm

### 例子 3: 修改 gl, pa, etc. 用户的password
不用 shutdown app tier

FNDCPASS apps/password 0 Y system/manager ALLORACLE password


FNDLOAD 可以用来在不同的环境中copy configuration。例如，业务人员可能在
UAT 设好config，可以用这个工具download 下来再传给Production，这样就避免
人工输入可能出现的错误。 下面举几个例子：

- Using FNDLOAD to download Concurrent Program definition

select APPLICATION_ID, CONCURRENT_PROGRAM_NAME
from fnd_concurrent_programs
where CONCURRENT_PROGRAM_NAME='FNDSCURS';

select APPLICATION_SHORT_NAME
from fnd_application where APPLICATION_ID = 0;

### download the definition to file myACTIVEUSER.ldt
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct myactiveuser.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="FNDSCURS"

- Using FNDLOAD to download Lookup type and its value <419136.1>
select LOOKUP_TYPE from FND_LOOKUP_TYPES  where lookup_type like 'FND%' order by 1

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct mylookup.ldt FND_LOOKUP_TYPE VIEW_APPSNAME=FND LOOKUP_TYPE="FND_CP_PROGRAM_TYPE"


- Using FNDLOAD to download profile options set on responsibiity level <566865.1>

select RESPONSIBILITY_KEY from FND_RESPONSIBILITY ;
select PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS
where PROFILE_OPTION_NAME like 'FND%'  ;
select USER_PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS_VL
where USER_PROFILE_OPTION_NAME like 'FND%';

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct myresp.ldt FND_RESPONSIBILITY RESP_KEY="SYSTEM_ADMINISTRATOR"

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="ICX_SESSION_TIMEOUT" APPLICATION_SHORT_NAME="ICX"
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="FND_DIAGNOSTICS" APPLICATION_SHORT_NAME="FND"


===========================================


12. statistics
=======================================================

Oracle Applications 并不直接用 DBMS_STATS 来 收集数据库的statistics，而是用自己的工具
FND_STATS。

例子如下：
# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics('MRP');                    <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL');                    <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table

SQL> begin                                                             <- Partitioned table
  2      fnd_stats.gather_table_stats(ownname     => 'APPLSYS',
  3                                   tabname     => 'WF_ITEM_ACTIVITY_STATUSES',
  4                                   granularity => 'PARTITION');
  5  end;
  6  /

select PARTITION_NAME from all_tab_partitions where upper(TABLE_NAME) ='GL_JE_LINES';

exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_BATCHES',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');
exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_HEADERS',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');

exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,'JAN09','NOBACKUP',TRUE,'PARTITION');

### check global stats
select table_name, last_analyzed from dba_tables where table_name ='GL_JE_LINES'
### gather table stats
exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,NULL,'NOBACKUP',TRUE,'ALL');

### check which columns do histogram                              
select table_name,column_name,hsize from applsys.fnd_histogram_cols;


参考资料：
<419728.1> How To Gather Statistics On Oracle Applications 11.5.10(and above)
<169935.1> Troubleshooting Oracle Applications Performance Issues
<429002.1> histogram collection