dbms_stats 导入导出 schema 级别统计信息

BEGIN
   DBMS_STATS.gather_schema_stats (ownname            => 'SCOTT',
                                   options            => 'GATHER AUTO',
                                   estimate_percent   => DBMS_STATS.auto_sample_size,
                                   method_opt         => 'for all columns size repeat',
                                   degree             => 8);
END;
/

--下面将数据库mmbo5上scott的统计信息导入到另外一个数据库mmbo4下的scott中
a、收集统计信息
scott@MMBO5> delete from emp where deptno=20;

scott@MMBO5> commit;

scott@MMBO5> exec dbms_stats.gather_schema_stats('SCOTT',cascade=>true);

scott@MMBO5> select table_name,num_rows,last_analyzed from user_tables;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
DEPT                                    4 20130513 22:19:10
EMP                                     9 20130513 22:19:10      --->emp表被删除了5条记录还剩9条
BONUS                                   0 20130513 22:19:10
SALGRADE                                5 20130513 22:19:10
TT                                      4 20130513 22:19:10

b、创建用于存储统计信息的表
--可以指定表存放于哪个表空间，如果缺省则存放到用户的缺省表空间
scott@MMBO5> exec dbms_stats.create_stat_table('SCOTT', 'STATS_TABLE');

PL/SQL procedure successfully completed.

c、导出scott的统计信息到stats_table
scott@MMBO5> exec dbms_stats.export_schema_stats('SCOTT','STATS_TABLE','SCOTT');

PL/SQL procedure successfully completed.

scott@MMBO5> exit

d、导出统计信息到dump文件
oracle@Dev-DB-04:~> expdp scott/tiger directory=db_dump_dir dumpfile=st.dmp logfile=st.log tables=STATS_TABLE

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 13 May, 2013 22:20:53

Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STATS_TABLE"                       17.82 KB      74 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /u02/database/MMBO5/BNR/dump/st.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 22:21:11

e、ftp统计信息到目的服务器并导入dump文件
--由于下面是在同一台服务器，所以直接用cp命令复制dump文件到特定目录
oracle@Dev-DB-04:~> cp /u02/database/MMBO5/BNR/dump/st.dmp /u02/database/MMBO4/BNR/dump

f、使用datapump impdp导入dmp文件到目的schema
oracle@Dev-DB-04:~> export ORACLE_SID=MMBO4
oracle@Dev-DB-04:~> impdp scott/tiger directory=db_dump_dir dumpfile=st.dmp logfile=imp_st.log tables=STATS_TABLE

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 13 May, 2013 22:22:57
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."STATS_TABLE"                       17.82 KB      74 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 22:23:03

g、导入统计信息到目标数据库的schema
--导入前先查看一下本数据库scott的统计信息
scott@MMBO4> select table_name,num_rows,last_analyzed from user_tables;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
STATS_TABLE
DEPT                                    4 20130513 22:16:05
EMP                                    14 20130513 22:16:05
BONUS                                   0 20130513 22:16:05
SALGRADE                                5 20130513 22:16:05
T                                   49991 20130513 22:16:20

6 rows selected.

--导入统计信息
scott@MMBO4> exec dbms_stats.import_schema_stats('SCOTT','STATS_TABLE','SCOTT');

PL/SQL procedure successfully completed.

--可以通过查询视图dba_optstat_operations获得目标数据库上统计信息日志
ALTER SESSION SET nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss';
COL start_time FORMAT a14
COL end_time FORMAT a14
COL operation FORMAT a30
COL target FORMAT a30
SET LINESIZE 120

SELECT operation,
       target,
       start_time,
       end_time
  FROM dba_optstat_operations
 WHERE operation LIKE 'import%';

OPERATION                      TARGET                         START_TIME     END_TIME
------------------------------ ------------------------------ -------------- --------------
import_schema_stats            SCOTT                          2013-05-13 22: 2013-05-13 22:
                                                              24:23          24:23

--Author: Robinson
--Blog  : http://blog.csdn.net/robinson_0612
                                                               
--下面的查询也可以看到此时scott下LAST_ANALYZED已经被更新为与源服务器上的统计信息相同
scott@MMBO4> select table_name,num_rows,last_analyzed from user_tables;

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
STATS_TABLE
DEPT                                    4 20130513 22:19:10
EMP                                     9 20130513 22:19:10
BONUS                                   0 20130513 22:19:10
SALGRADE                                5 20130513 22:19:10
T                                   49991 20130513 22:16:20

6 rows selected.

scott@MMBO4> select count(*) from emp;

  COUNT(*)
----------
        14
--实际上在mmbo4上表emp的数据依旧为14条，但统计信息为9条，是来自源数据库的统计信息
--对于源数据库schema上存在，但目标数据库schema不存在的对象，如mmbo5上的表tt，在mmbo4上不会被导入
--对于源数据库schema上不存在，但目标数据库schema存在的对象，如mmbo4上的表t，其统计信息没有被更新

h、根据需要可以考虑是否清除存储统计信息的表
scott@MMBO4> exec dbms_stats.drop_stat_table('SCOTT','STATS_TABLE');

PL/SQL procedure successfully completed.

--对于系统级别的统计信息的导入导出，此处不做演示，需要注意的是应使用dbms_stats中相应的导入导出procedure.
--dbms_stats.gather_system_stats
--dbms_stats.import_system_stats
--dbms_stats.export_system_stats