How to Export & Import Statistics in Oracle Database

There are several reasons you might need to export Oracle object statistics. For instance, you may want to back up the current statistics before gathering new ones for a large set of objects, or you might need to transfer statistics from a production environment to a test environment. This ensures that the Cost-Based Optimizer (CBO) generates consistent execution plans in both environments.

Regardless of the reason, the first step is to create a table that will store the statistics to be exported:

1. Create Statistics Table

To create the statistics table, use the following procedure:

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('W3BUDDY', 'STAT_TABLE', 'SYSAUX');

Once the table is created, you can export a single table, an entire schema, or even the whole database into that table.

2. Export Table Statistics

To export statistics for a specific table, use the DBMS_STATS.EXPORT_TABLE_STATS procedure:

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=>'W3BUDDY', CASCADE=>TRUE);

This procedure will export the statistics of the EMP table under the SCOTT schema into the STAT_TABLE located under the W3BUDDY schema.

If the statistics table is in a schema other than the owner of the table whose statistics are being exported, you must explicitly use the STATOWN parameter.

3. Export Schema Statistics

To export statistics for all objects within a schema, use the DBMS_STATS.EXPORT_SCHEMA_STATS procedure:

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=>'W3BUDDY');

This code will export all object statistics under the SCOTT schema and insert them into the STAT_TABLE table, which resides under the W3BUDDY schema.

4. Export Database Statistics

To export statistics for the entire database, use the DBMS_STATS.EXPORT_DATABASE_STATS procedure:

SQL> EXEC DBMS_STATS.EXPORT_DATABASE_STATS(STATTAB=>'STAT_TABLE', STATOWN=>'W3BUDDY');

Note: There are also other export procedures, such as EXPORT_COLUMN_STATS, EXPORT_DICTIONARY_STATS, EXPORT_FIXED_OBJECTS_STATS, EXPORT_INDEX_STATS, and EXPORT_SYSTEM_STATS.

5. Import Table Statistics

To import table statistics, use the DBMS_STATS.IMPORT_TABLE_STATS procedure:

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=>'W3BUDDY', CASCADE=>TRUE);

6. Import Schema Statistics

To import schema statistics, use the DBMS_STATS.IMPORT_SCHEMA_STATS procedure:

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   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_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STAT_TABLE', STATOWN=>'W3BUDDY');

7. Import Database Statistics

To import database statistics, use the DBMS_STATS.IMPORT_DATABASE_STATS procedure:

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=>'W3BUDDY');

By following the steps above, you can easily export and import statistics in Oracle databases, enabling you to backup, transfer, and restore statistics across different environments, ensuring that the database optimizer operates consistently in test or production settings.