Export and Import schema Statistics in Oracle Database

There may be times that you wish to save you database statistics. Perhaps to use in a test system or prior to a code release that may ultimately change your statistics and change you query plans for the worse. By exporting the statistics, they can be saved for future importing.

Source Database : srcdb ( host- example01)
Target database : trgdb ( host - example02)

1) To generate schema statistics and import them into a separate database:

SQL> select instance_name,host_name,status from v$instance;

INSTANCE_NAME    HOST_NAME                      STATUS
---------------- ------------------------------ ------------
srcdb           example01    OPEN

SQL> 

On the source host, start SQL*Plus and connect to the source database as administrator.
Create a table to hold the schema statistics.

For example, execute the following PL/SQL program to create user statistics table opt_stats:

SQL> BEGIN
  DBMS_STATS.CREATE_STAT_TABLE ( 
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/

PL/SQL procedure successfully completed.

2) Gather schema statistics.

For example, manually gather schema statistics as follows:

-- generate representative workload
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

3) Use DBMS_STATS to export the statistics.

For example, retrieve schema statistics and store them in the opt_stats table created previously:

SQL> BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS (
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/

PL/SQL procedure successfully completed.


4) Use Oracle Data Pump to export the contents of the statistics table.

For example, run the expdp command at the operating schema prompt:

SQL> create or replace directory dump_dir as '/u09/backup/export';

Directory created.

$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats

e.g.,
[oracle@example01 export]$ expdp DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats

Export: Release 12.2.0.1.0 - Production on Wed Feb 5 12:40:39 2020

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA DIRECTORY=dump_dir DUMPFILE=SCOTT_stat.dmp logfile=SCOTT_stat_expdp.log TABLES=SCOTT.opt_stats
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.125 MB
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 "SCOTT"."OPT_STATS"                      1.534 MB   10486 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u03/work/export/SCOTT_stat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 5 12:44:09 2020 elapsed 0 00:03:24


5) Transfer the dump file to the target database host.

$ scp gmohapatra@example01:/u03/work/export/*.dmp .

6) Log in to the target host, and then use Oracle Data Pump to import the contents of the statistics table.

SQL> create or replace directory dump_dir1 as '/u08/backup/import';

Directory created.

For example, run the impdp command at the operating schema prompt:

$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats 
e.g.,
[oracle@example02 import]$ impdp DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats

Import: Release 12.2.0.1.0 - Production on Wed Feb 5 13:30:16 2020

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA DIRECTORY=dump_dir1 DUMPFILE=SCOTT_stat.dmp logfile=impdp_SCOTT_stat.log TABLES=SCOTT.opt_stats
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."OPT_STATS"                      1.534 MB   10486 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 "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 5 13:30:43 2020 elapsed 0 00:00:20

[oracle@example02 import]$

7) On the target host, start SQL*Plus and connect to the target database.
Use DBMS_STATS to import statistics from the user statistics table and store them in the data dictionary.

The following PL/SQL program imports schema statistics from table opt_stats into the data dictionary:

SQL> BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS(
    ownname => 'SCOTT'
,   stattab => 'opt_stats'
);
END;
/  

PL/SQL procedure successfully completed.

SQL> select name from v$database;

NAME
---------
trgdb

SQL>

-- If you want to lock the statistics, below dbms api can be used.

SQL> exec dbms_stats.LOCK_SCHEMA_STATS('SCOTT');

PL/SQL procedure successfully completed.

SQL>

Note: You can do export and import statistics of whole database or for few tables also.