有很多时候我们不方便在数据量很大的数据库做分析，这样可能会在测试库上做完分析后把统计信息应用到生产库中去。我们可以用下面的脚本来实现：
--1.create a table for storage the statistics information on test db
SQL> exec dbms_stats.create_stat_table('WACOS','STATS_TABLE');
PROCEDURE CREATE_STAT_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
TBLSPACE VARCHAR2 IN DEFAULT
GLOBAL_TEMPORARY BOOLEAN IN DEFAULT
--2. Gather the statistics information on test db（如果对象多就很耗时）
SQL> exec dbms_stats.gather_schema_stats('WACOS');
PROCEDURE GATHER_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
OPTIONS VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
GATHER_TEMP BOOLEAN IN DEFAULT
GATHER_FIXED BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
--3. Confirm statistics information on test db
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> select table_name,last_analyzed from dba_tables where owner = 'WACOS' order by last_analyzed desc;
--4. Export the statistics information to table STATS_TABLE on test db（如果对象多就耗时）
SQL> exec dbms_stats.export_schema_stats('WACOS','STATS_TABLE');
PROCEDURE EXPORT_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
--5. Check the statistics information on test db
SQL> select count(*) from stats_table;
--6. Delete the statistics information on product db
SQL> exec dbms_stats.delete_schema_stats('WACOS');
PROCEDURE DELETE_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
--7. export table stats_table on test db and import to product db
exp/imp
--8. Import the statistics information on product db
SQL> exec dbms_stats.import_schema_stats('WACOS','STATS_TABLE');
PROCEDURE IMPORT_SCHEMA_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
FORCE BOOLEAN IN DEFAULT
--9. Check the statistics on product db
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> select table_name,last_analyzed from dba_tables where owner = 'WACOS' order by last_analyzed desc;
--10. Drop the statistics table
SQL> exec dbms_stats.drop_stat_table('WACOS','STATS_TABLE');
PROCEDURE DROP_STAT_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
