If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful.

Here i will take export of statistics of a table RAJ.TEST from PROD and import into TEST

DEMO:

 

create a table to store the stats:

--- RAJ is the owner of the stats table, STAT_TEST - name of the stats table

PROD> exec DBMS_STATS.CREATE_STAT_TABLE('RAJ','STAT_TEST','SYSAUX');

PL/SQL procedure successfully completed.

SQL> ;
  1* select owner,table_name from dba_tables where table_name='STAT_TEST'
SQL> /

OWNER	     TABLE_NAME
------------ ------------
RAJ	     STAT_TEST

SQL> 

Now export the statistics of the table RAJ.TEST to stats table ( STAT_TEST)

PROD> exec dbms_stats.export_table_stats(ownname=>'RAJ', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

 

Now take expdp of this stats table(STAT_TEST)

[oracle@PROD]$ expdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1

Export: Release 12.1.0.2.0 - Production on Fri Aug 28 10:09:26 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "RAJ"."SYS_EXPORT_TABLE_01":  raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "RAJ"."STAT_TEST"                           19.24 KB      19 rows
Master table "RAJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DIR1/stats.dmp
Job "RAJ"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 28 10:12:33 2015 elapsed 0 00:03:00

 

Move the dump file from PROD to TEST machine and import the same

 

[oracle@TEST]$ impdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Fri Aug 28 10:12:42 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "RAJ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RAJ"."SYS_IMPORT_TABLE_01":  raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RAJ"."STAT_TEST"                           19.24 KB      19 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "RAJ"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 28 10:14:36 2015 elapsed 0 00:01:42

 

Now import the statistics in database:

 

SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
RAJ	     TEST	  05-AUG-15


SQL> exec dbms_stats.import_table_stats(ownname=>'RAJ', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER	     TABLE_NAME   LAST_ANAL
------------ ------------ ---------
RAJ	     TEST	  28-AUG-15

We can observe after import_table_stats, last_analyzed date has been updated.

We can do export/import statistics at schema level and database level:

For database :

----For export
 exec dbms_stats.EXPORT_DATABASE_STATS('','','');

--- For import
 exec dbms_stats.IMPORT_DATABASE_STATS('','','');

For schema:

--- For export
exec  dbms_stats.export_schema_stats( ownname=>, stattab=>'', statid=>'' );

--- For import
exec  dbms_stats.import_schema_stats( ownname=>, stattab=>'', statid=>'' );