How to export and import schema stats from one database to another database in oracle

How to import schema stats from one database to another database in oracle:

It is not just importing schema stats from one database to another database, the one of the problem I have come across is export delay or import delay when export or importing whole schema stats with including statistics. 

I found this is useful when making my export and imports faster however the solution is FYI only, you can test and see it yourself.

The steps in exporting and importing schema stats  in summary:

Create the STATS table.

Export the statistics to the STATS table.

Export the STATS table using export(exp) or datapump(expdp)

Transfer the dump file to the destination database.

Import the STATS table to the destination database.

In detail:

Please note to generate stats on the schema as you see fit prior to export and import just in case.

you may use code like this or can be added to more options.. 

begin

    dbms_stats.gather_schema_stats('MYSCHEMA_DEV');

end;

/

1. Create the STATS table.

begin

    dbms_stats.create_stat_table( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

2. Export the statistics to the STATS table.

begin

    dbms_stats.export_schema_stats( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

3. Export the STATS table using export(exp) or datapump(expdp)

this can be along with schema export or only stats table can be exported

1. expdp command.

2. dbms_datapump API procedures. 

expdp SCHEMAS=MYSCHEMA_DEV DIRECTORY=dumps parallel=24 DUMPFILE=MYSCHEMA_DEV_%U.dmp LOGFILE=MYSCHEMA_DEV_exp.log COMPRESSION=ALL CLUSTER=N METRICS=Y LOGTIME=ALL  EXCLUDE=STATISTICS

4. Transfer the dump file to the destination database.

use sftp or winscp whatever you think is fit.

5. Import the STATS table to the destination database.

begin

    dbms_stats.import_schema_stats(ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

Hope this helps... please feel free to comment .. thanks.