Export Oracle Schema Statistics

Hey,

We sometimes need to backup up the schema statistics. Mainly before

1.) Any major change and release at database.
2.) Before upgrade.
3.) Migration.

In any above event, you may some event like performance issue to execution plan changed, you need to import the old statistics.


A.] Create a stat table:

Syntax:

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

Example:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP','ASHISH_DATA');

You can also use:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

B.] Verify if you have statistics table in DBA_OBJECTS

SQL> select owner,OBJECT_NAME,CREATED, object_type from dba_objects where object_name='ENOVIA_SCHEMA_STATS_BACKUP';

OWNER      OBJECT_NAME                                                      CREATED            OBJECT_TYPE
----------- ---------------------------------------------------       ------------------       -------------------
ASHISH    ASHISH_SCHEMA_STATS_BACKUP           03-FEB-17          TABLE
ASHISH    ASHISH_SCHEMA_STATS_BACKUP           03-FEB-17          INDEX

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

  COUNT(*)
----------
         0


C.] Export schema statistics to statistics table:


SQL> exec dbms_stats.export_schema_stats('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

PL/SQL procedure successfully completed.

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

  COUNT(*)
----------
     77953



Best of luck