Copy optimizer table statistics between different schema and databases

This test case is to simulate the situation in which we need to copy a table stats from one schema to anohter schema in a different database.

Summary of Steps:
1. Set up the initial tables and collect stats
2. Export table stats using dbms_stats package in the source database and schema
3. Exp the stats table from source database
4. Imp the stats table to the target database and target schema
5. Import table stats using dbms_stats package in target database

Note: Pay attention to the neccessity of updating c5 in the stats tab to replace source schema name with target schema name.


1. set up the inital tables
Note: Table t is in hr, we want to copy its stats to table t in scott

scott@TEST10G> @1_setup
scott@TEST10G> connect hr/hr;
Connected.
hr@TEST10G>
hr@TEST10G> drop table t;

Table dropped.

hr@TEST10G>
hr@TEST10G> create table t
  2  as
  3  select * from all_objects;

Table created.

hr@TEST10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

hr@TEST10G>
hr@TEST10G> grant select on t to scott;

Grant succeeded.

hr@TEST10G>
hr@TEST10G> connect scott/tiger;
Connected.
scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t
  2  as
  3  select * from hr.t;

Table created.

2. Export table stats

hr@TEST10G> conn hr/hr
Connected.
hr@TEST10G>
hr@TEST10G> drop table t_stats;

Table dropped.

hr@TEST10G> exec dbms_stats.create_stat_table(user, 'T_STATS');

PL/SQL procedure successfully completed.

hr@TEST10G> exec dbms_stats.export_table_stats(user, 'T', stattab => 'T_STATS');

PL/SQL procedure successfully completed.

3. Exp the stats table: T_STATS

exp hr/hr tables=t_stats file=t_stats.dmp log=3_exp_t_stats.log

$ 3_exp_t_stats.sh

Export: Release 10.2.0.1.0 - Production on Mon Nov 10 18:51:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        T_STATS         14 rows exported
Export terminated successfully without warnings.

4. Imp the stats table: T_STATS
imp system/oracle fromuser=hr touser=scott tables=t_stats file=t_stats.dmp log=4_imp_t_stats.log

Import: Release 10.2.0.1.0 - Production on Mon Nov 10 18:52:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing HR's objects into SCOTT
. . importing table "T_STATS" 14 rows imported
Import terminated successfully without warnings.

5. Import table stats
Note: to update c5 in stats table is necessary !

system@TEST10G> @5_imp_tab_stats.sql
system@TEST10G> connect scott/tiger
Connected.
scott@TEST10G>
scott@TEST10G> -- must update column c5
scott@TEST10G>
scott@TEST10G> Prompt c5 stores schema name
c5 stores schema name
scott@TEST10G> select c5 from t_stats;

C5
------------------------------
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR

14 rows selected.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> --  see what will happen if we don't update c5
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER             TABLE_NAME      NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------- -------------------
HR                T                  40733 2008-11-10 18:59:31
SCOTT             T

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> -- update c5
scott@TEST10G>
scott@TEST10G> update t_stats set c5='SCOTT';

14 rows updated.

scott@TEST10G>
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER          TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- -------------- ---------- -------------------
HR             T                   40733 2008-11-10 18:59:31
SCOTT          T                   40733 2008-11-10 18:59:31