BACKUP_SCHEMA_STATS&RESTORE_SCHEMA_STATS

R12: PA: "PRC: Update Project And Resource Base Summaries" Gives "APPLSYS"."FND_STATTAB" Is Too Old Error (Doc ID 2221103.1)
How To Collect Histograms In E-business Suite Using FND_STATS (Doc ID 429002.1)
Version of Statistics Table APPLSYS.FND_STATTAB IS TOO OLD When Upgrading Configurator (Doc ID 734888.1)
Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)
Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime (Doc ID 1581549.1)
Export/Import Process for Oracle E-Business Suite Release 12.1 Database Instances Using Oracle Database 19c (Doc ID 2615617.1)

--------------------
sqlplus / as sysdba
begin
  dbms_stats.gather_schema_stats('SYS',
                                 options          => 'GATHER STALE',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
                                 cascade          => TRUE);
end;
/
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
--------------------升级STATS表
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS', 'FND_STATTAB'); 
Commit;

--------------------Gather statistics into a table
From the administration server node, use SQL*Plus to connect to the database and run the following commands to gather statistics of all schemas.

SQL> connect apps/<apps password>
SQL> exec fnd_stats.gather_schema_statistics(schemaname=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,options=>'GATHER AUTO');

Then, as the apps user, run the following script:
set verify off
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;
DECLARE
BEGIN
  FND_STATS.BACKUP_SCHEMA_STATS(schemaname => 'ALL', statid => '<your identifier>');
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20000, sqlerrm ||' Error while executing FND_STATS.BACKUP_SCHEMA_STATS package.');
END;
/

exit;

###############Create target statistics
Use SQL*Plus to connect to the target PDB as SYSTEM and use the following commands to gather system and fixed object statistics, and upgrade the FND_STATTAB statistics table:

$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus system/<system password>;
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> exec dbms_stats.upgrade_stat_table('APPLSYS','FND_STATTAB');

Then, as the apps user, run the following script:

set verify off
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;
DECLARE
BEGIN
  FND_STATS.RESTORE_SCHEMA_STATS(schemaname => 'ALL', statid => '<your identifier>');
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20000, sqlerrm ||' Error while executing FND_STATS.BACKUP_SCHEMA_STATS package.');
END;
/

exit;

###############
运行并导出统计数据收集
演练阶段，运行统计数据收集后导出统计信息：
connect apps/<apps password>
execute fnd_stats.backup_schema_stats('ALL','bz_2023');
exp userid=APPLSYS/<apps password> file=fnd_stats_bz_2023.dmp table=fnd_stattab log=fnd_stats_bz_2023.log

生产割接阶段导入统计数据信息：
imp userid=APPLSYS/<apps password> file=fnd_stats_bz_2023.dmp full=y ignore=y log=fnd_stats_bz_imp.log
connect apps/<apps password>
execute fnd_stats.restore_schema_stats('ALL','bz_2023');

这个不包含cux schema
在演练环节做FND统计收集，然后备份导出这个结果，在生产环节导入。不是从源库备份导出的