Exporting and importing statistics

Exporting and importing statistics from PROduction to PREproduction:
====================================================================

First we must create a table to save the statistics:

execute dbms_stats.create_stat_table('SYS','ESTADISTICAS','TOOLS');
^schema ^table ^TS

Second we export the schema statistics to this table:

execute dbms_stats.EXPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYS');

We can, alternativelly, export only one table:

PROCEDURE EXPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT

execute dbms_stats.EXPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTADISTICAS','a','SYS');

Next we export the statistics table and import it in the destination instance:

EXP sys@PRO file=estad_pro.dmp log=estad_pro.log tables="ESTADISTICAS"

Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Exportaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16

Exportando las tablas especificadas a travÚs de la Ruta de Acceso Convencional .
..
. exportando la tabla ESTADISTICAS 100058 filas exportadas
La exportaci¾n ha terminado correctamente y sin advertencias.

IMP system@PRE file=estad_pro.dmp log=estad_pre.log full=Y

importaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16
. importando objetos de SYS en SYSTEM
. importando objetos de SYS en SYSTEM
. importando la tabla "ESTADISTICAS" 100058 filas importadas

La importaci¾n ha terminado correctamente y sin advertencias.


PROCEDURE IMPORT_SCHEMA_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
FORCE BOOLEAN IN DEFAULT

execute dbms_stats.IMPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYSTEM');
Procedimiento PL/SQL terminado correctamente.

PROCEDURE IMPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT

Or we can do the same procedure with the individual table:

SQL> execute dbms_stats.IMPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTAD
ISTICAS','DWH03',TRUE,'SYSTEM');

Procedimiento PL/SQL terminado correctamente.

And, finally, we can drop (or not) the statistics table in the source instance:

execute dbms_stats.drop_stat_table('SYS','ESTADISTICAS');