How to export statistics in Oracle

To export statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics. Here are the basic steps:

(1) Connect to your database as a user with the necessary privileges (such as SYS or SYSTEM).

(2) create a stat table to hold the statistics

EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT','STATTAB');

(3) Now suppose you want to export a particular table stats, then Invoke the DBMS_STATS package using the below package

DBMS_STATS.EXPORT_TABLE_STATS

Syntax

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

EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP', STATTAB=>'STATTAB',STATID=>'EXP_1',STATOWN=>'SCOTT);

You can export statistics at multiple instances and then later compare them also

We can also use exported statistics to import into another having the same table

(3) Now suppose you want to export statistics for the whole schema,

then Invoke the DBMS_STATS package using the below package

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

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STATTAB',STATID=>'EXP_SC_1',STATOWN=>'SCOTT');

(4) Now suppose you want to export stats for a particular index only,then Invoke the DBMS_STATS package using the below package

DBMS_STATS.EXPORT_INDEX_STATS (
   ownname  VARCHAR2, 
   indname  VARCHAR2, 
   partname VARCHAR2 DEFAULT NULL,
   stattab  VARCHAR2, 
   statid   VARCHAR2 DEFAULT NULL,
   statown  VARCHAR2 DEFAULT NULL);

Example

EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'SCOTT',INDNAME=>'EMP_IDX', STATTAB=>'STATTAB',STATID=>'EXP_IND_1',STATOWN=>'SCOTT);

Please be aware that you need the necessary privileges to execute these procedures.

I hope you like this article on How to export statistics in Oracle

Related Articles

How to import statistics in Oracle
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to delete statistics from Table in Oracle 
How to check column statistics in Oracle