Create Stat table, Export Stats, and Set table stat

Connect as sysdba and run this procedure to export the statistics to user defined statistics table i.e STATS_EMP in this example.

SQL> exec dbms_stats.export_table_stats ( -
> ownname => 'SCOTT', -
> tabname => 'EMP', -
> partname => NULL, -
> stattab => 'STATS_EMP', -
> statid => NULL, -
> cascade => TRUE, -
> statown => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
SQL> select * from STATS_EMP;

Transfering Stats
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:

SQL> EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
SQL> EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');

This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

SQL> EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
SQL> EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');

DBMS_STATS.SET_TABLE_STATS
exec DBMS_STATS.SET_TABLE_STATS ( -
ownname => 'SCOTT', -
tabname => 'DEPT', -
stattab => 'STATS_DEPT', -
statid => NULL, -
numrows => NULL, -
numblks => NULL, -
avgrlen => NULL, -
flags => NULL, -
statown => 'SCOTT');

SQL> @/database/test1/scripts/set_table_stats.sql

PL/SQL procedure successfully completed.

[oracle@vinay scripts]$ export ORACLE_SID=test1
[oracle@vinay scripts]$ ./gather_tab_stat.sh
Table: DEPT Completed

SQL> select count(*) from stats_dept;

COUNT(*)
----------
1