Oracle : Move Stats between databases (eg. prod to nonprod)

How to Move Oracle database Stats between databases

Why Move Stats ?

1. Performance issues between similar environments.
2. To compare generated plans between two stats. 

Steps: 1. Create stat table in source database(eg. prod)

Syntax:
EXEC DBMS_STATS.CREATE_STAT_TABLE (OWNNAME => '<Schema OWNER>', STATTAB => '<STAT_TABLENAME>',TBLSPACE =>'<tablespace>');

Sample cmd:
EXEC DBMS_STATS.CREATE_STAT_TABLE (OWNNAME => '&OWNER', STATTAB => 'STAT_&TAB',TBLSPACE =>'CMPGN_DATA_128K');
EXEC DBMS_STATS.CREATE_STAT_TABLE (OWNNAME => 'IONIX_EVENTS', STATTAB => 'STAT_&TAB');



Steps: 2. Export table/s stats deployed in source  to the stat table :

Syntax:
exec DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => '<OWNER>', TABNAME => '<TABLE>', statown=>'<STATOWNERschema>',STATTAB => '<STAT_TABLENAME>', statid => NULL,CASCADE => TRUE);

Sample cmd:
exec DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'CMPGN', TABNAME => '&TAB', statown=>'DBA_USER',STATTAB => 'STAT_&TAB', statid => NULL,CASCADE => TRUE);

****Export as many tables as you want

Step 3. Export the the stat table to target database or schema.One could use expdp or exp to move the stat table.

Step 4: Import Stat table dmp into target database/schema, use impdp or imp.

Step 5: Import the stat from stat table to tables in  target database.

Syntax:

exec DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME => '<SCHEMA>', TABNAME => '<TABLE>',partname=>'<PARTITION/skip ifnot required>', STATTAB => 'STATTABLE', statid => NULL, CASCADE => TRUE, no_invalidate => true);

Sample cmd:

exec DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME => 'PGN', TABNAME => 'TRM_M',partname=>'PD', STATTAB => 'STATO_TM', statid => NULL, CASCADE => TRUE, no_invalidate => true);

**This is still being refined, check for exact syntax/option in database if something doesn't work.