Use import statistics to save database migration downtime

drop table oracms.stat_table;
exec dbms_stats.create_stat_table('ORACMS','STAT_TABLE');

-- backup existing stats
exec dbms_stats.export_schema_stats(ownname=>'DBACMS',stattab=>'STAT_TABLE',statid=>'run_init',statown=>'ORACMS');
-- gather new stats
exec dbms_stats.gather_schema_stats(ownname=>'DBACMS');

-- export newly gathered stats
exec dbms_stats.export_schema_stats(ownname=>'DBACMS',stattab=>'STAT_TABLE',statid=>'run_new',statown=>'ORACMS');

select statid, d1, count(*) from oracms.STAT_TABLE where c5 ='DBACMS' group by statid, d1;

SQL> select last_analyzed, avg_row_len, num_rows from dba_tables where table_name='VAS_INFO';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
26-AUG-08 57 12763481

SQL> select last_analyzed, avg_row_len, num_rows,owner from dba_tables where table_name='VAS_INFO';

LAST_ANAL AVG_ROW_LEN NUM_ROWS OWNER
--------- ----------- ---------- ------------------------------
26-AUG-08 57 12763481 DBACMS

SQL> set timing on echo on time on
09:02:36 SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('DBACMS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.57
09:02:51 SQL> select last_analyzed, avg_row_len, num_rows from dba_tables where table_name='VAS_INFO';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------


Elapsed: 00:00:00.01
09:03:08 SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('DBACMS','STAT_TABLE',statid=>'run_new',statown=>'ORACMS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.53
09:03:25 SQL> select last_analyzed, avg_row_len, num_rows from dba_tables where table_name='VAS_INFO';

LAST_ANAL AVG_ROW_LEN NUM_ROWS
--------- ----------- ----------
26-AUG-08 57 12740519

Elapsed: 00:00:00.01