转载自雅飞士个人博客 本文链接地址: http://blog.yafeishi.net/2012/12/stat-export-import.html
【oracle】统计信息的导入导出
统计信息导入导出试验.
涉及的具体语法可参考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
