How To Export And Import Statistics In Oracle

How To Export And Import Statistics In Oracle

Step 1:
If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful.

Here i will take export of statistics of a table ARVIND.TEST from PROD and import into TEST

DEMO:


create a table to store the stats:

--- ARVIND is the owner of the stats table, STAT_TEST - name of the stats table

PROD> exec DBMS_STATS.CREATE_STAT_TABLE('ARVIND','STAT_TEST','SYSAUX');

PL/SQL procedure successfully completed.

SQL>  select owner,table_name from dba_tables where table_name='STAT_TEST';
SQL> /

OWNER      TABLE_NAME
------------ ------------
ARVIND      STAT_TEST

SQL>

PROD> exec dbms_stats.export_table_stats(ownname=>'ARVIND', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.


Now take expdp of this stats table(STAT_TEST)


[oracle@PROD]$ expdp dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1

Export: Release 12.1.0.2.0 - Production on Fri Dec 28 10:09:26 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: ARVIND/ARVIND@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "ARVIND"."SYS_EXPORT_TABLE_01":  ARVIND/********@orcl dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "ARVIND"."STAT_TEST"                           19.24 KB      19 rows
Master table "ARVIND"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARVIND.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DIR1/stats.dmp
Job "ARVIND"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 28 10:12:33 2019 elapsed 0 00:03:00



Move the dump file from PROD to TEST machine and import the same


[oracle@TEST]$ impdp dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1 table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Fri Dec 28 10:12:42 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: ARVIND/XXXXXX@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "ARVIND"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARVIND"."SYS_IMPORT_TABLE_01":  ARVIND/********@orcl dumpfile=stats.dmp logfile=stats.log tables=ARVIND.STAT_TEST directory=DIR1 table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARVIND"."STAT_TEST"                           19.24 KB      19 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "ARVIND"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 28 10:14:36 2019 elapsed 0 00:01:42


Now import the statistics in database:


SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER      TABLE_NAME   LAST_ANAL
------------ ------------ ---------
ARVIND      TEST   05-Dec-19


SQL> exec dbms_stats.import_table_stats(ownname=>'ARVIND', tabname=>'TEST',  stattab=>'STAT_TEST', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,last_analyzed from dba_tables where table_name='TEST';

OWNER      TABLE_NAME   LAST_ANAL
------------ ------------ ---------
ARVIND      TEST   28-Dec-19


We can observe after import_table_stats, last_analyzed date has been updated.

We can do export/import statistics at schema level or database level:

Note : use expdp to export the newly created stats table from source to target database.

Database Statistics

1. Log onto the database

sqlplus '/ as sysdba'
2. Import the database statistics

SQL> exec dbms_stats.IMPORT_DATABASE_STATS('<enter the name of the stats table>','<enter an identifier>','<enter the owner of the stats table>');
For example; exec dbms_stats.IMPORT_DATABASE_STATS('MYSTATS_TABLE','STATS_TEST_MOVE','MYUSER');



For schema:

Log onto the database

sqlplus '/ as sysdba'
2. Create a table to hold the exported statistics.

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