Export & Import Statistics

There might be many reasons to export the Oracle object statistics. For example, you may want to save the current statistics, that is to say, take a backup of statistics just before gathering huge number of objects statistics. Another reason might be performing realistic tests by transferring the production environment statistics to the test environment and forcing CBO to produce the same execution plans…

Whatever the reason is, the first step is to create a table to store the statistics to be exported:

CREATE STAT TABLE

SQL> exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a name for the stats table>','<tablespace to store the stats table');

# Example:

SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE('DBA_TAYSI' ,'STAT_TABLE' ,'SYSAUX');

Then we can either export a single table, a complete schema or even the whole database into that table:

EXPORT TABLE STATS

DBMS_STATS.EXPORT_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2, 
   partname        VARCHAR2 DEFAULT NULL,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   cascade         BOOLEAN  DEFAULT TRUE,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP', STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI', CASCADE=>TRUE);

That sql procedure will export the statistics of the table EMP which is under SCOTT schema into the table named STAT_TABLE that is under DBA_TAYSI schema.

If the stat table is defined under another schema than the owner of the table whose statistics is being exported, we need to explicitly use the parameter STATOWN.

EXPORT SCHEMA STATS

DBMS_STATS.EXPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   stattab         VARCHAR2,
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI');

This code will export all the object statistics under the SCOTT schema and insert them into the STAT_TABLE table which is residing under DBA_TAYSI schema.

EXPORT DATABASE STATS

DBMS_STATS.EXPORT_DATABASE_STATS (
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI');

Note: There is also EXPORT_COLUMN_STATS, EXPORT_DICTIONARY_STATS,
EXPORT_FIXED_OBJECTS_STATS, EXPORT_INDEX_STATS, EXPORT_SYSTEM_STATS.

IMPORT TABLE STATS

DBMS_STATS.IMPORT_TABLE_STATS (
   ownname         VARCHAR2, 
   tabname         VARCHAR2,
   partname        VARCHAR2 DEFAULT NULL,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   cascade         BOOLEAN  DEFAULT TRUE,
   statown         VARCHAR2 DEFAULT NULL,
   no_invalidate   BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
   force           BOOLEAN DEFAULT FALSE,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP',  STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI', CASCADE=>TRUE);

IMPORT SCHEMA STATS

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   no_invalidate   BOOLEAN DEFAULTto_no_invalidate_type(get_param('NO_INVALIDATE')),
   force           BOOLEAN DEFAULT FALSE,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI');

IMPORT DATABASE STATS

DBMS_STATS.IMPORT_DATABASE_STATS (
   stattab         VARCHAR2, 
   statid          VARCHAR2 DEFAULT NULL,
   statown         VARCHAR2 DEFAULT NULL,
   no_invalidate   BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
   force           BOOLEAN DEFAULT FALSE,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example:
SQL> EXEC DBMS_STATS.IMPORT_DATABASE_STATS(STATTAB=>'STAT_TABLE', STATOWN=>'DBA_TAYSI');