统计信息导入导出试验.
涉及的具体语法可参考oracle官方文档。
1. 数据库版本：
SQL> select * from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
2.创建测试表
SQL> create table test_stat as select * from dba_objects;
Table created
3.查看测试是否有统计信息：
SQL> select A.TABLE_NAME,A.NUM_ROWS,A.BLOCKS,A.LAST_ANALYZED from dba_tables a where a.table_name='TEST_STAT';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT
4.上步结果没有，故收集下统计信息：
SQL> execute dbms_stats.gather_table_stats(ownname => 'DANGHB',tabname => 'TEST_STAT',estimate_percent => 20,degree => 5,no_invalidate => false);
PL/SQL procedure successfully completed
5.再次查看统计信息：
SQL> select A.TABLE_NAME,A.NUM_ROWS,A.BLOCKS,A.LAST_ANALYZED from dba_tables a where a.table_name='TEST_STAT';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT 67780 1044 2012/12/6 11:
6.创建存放统计信息的表：
SQL> execute dbms_stats.create_stat_table(ownname => 'DANGHB',stattab => 'STAT_TABLE');
PL/SQL procedure successfully completed
7.导出统计信息：
SQL> execute dbms_stats.export_table_stats(ownname => 'DANGHB',tabname => 'TEST_STAT',stattab => 'STAT_TABLE');
PL/SQL procedure successfully completed
8.查看存放统计信息的表是否有内容：
SQL> select count(*) from stat_table;
COUNT(*)
----------
14
9.删除测试表的统计信息：
SQL> execute dbms_stats.delete_table_stats(ownname => 'DANGHB',tabname => 'TEST_STAT');
PL/SQL procedure successfully completed
10.确实是否删除：
SQL> select A.TABLE_NAME,A.NUM_ROWS,A.BLOCKS,A.LAST_ANALYZED from dba_tables a where a.table_name='TEST_STAT';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT
11.导入统计信息：
SQL> execute dbms_stats.import_table_stats(ownname => 'DANGHB',tabname => 'TEST_STAT',stattab => 'STAT_TABLE');
PL/SQL procedure successfully completed
12.查看是否导入成功：
SQL> select A.TABLE_NAME,A.NUM_ROWS,A.BLOCKS,A.LAST_ANALYZED from dba_tables a where a.table_name='TEST_STAT';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT 67780 1044 2012/12/6 11:
--EOF
原文地址：http://blog.yafeishi.net/2012/12/stat-export-import.html
