作者：阿离说你是宝贝 | 来源：互联网 | 2014-07-08 01:11
oracle统计信息的导入导出统计信息导入导出试验.www.2cto.com涉及的具体语法可参考oracle官方文档。1.数据库版本：SQL>select*fromv$version2/BANNER---------------------------------------...
oracle统计信息的导入导出
统计信息导入导出试验.
www.2cto.com
涉及的具体语法可参考oracle官方文档。
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.创建测试表 www.2cto.com
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=&＃39;TEST_STAT&＃39;;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT
4.上步结果没有，故收集下统计信息：
SQL> execute dbms_stats.gather_table_stats(ownname => &＃39;DANGHB&＃39;,tabname => &＃39;TEST_STAT&＃39;,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=&＃39;TEST_STAT&＃39;;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT 67780 1044 2012/12/6 11:
6.创建存放统计信息的表：
SQL> execute dbms_stats.create_stat_table(ownname => &＃39;DANGHB&＃39;,stattab => &＃39;STAT_TABLE&＃39;);
PL/SQL procedure successfully completed
7.导出统计信息：
SQL> execute dbms_stats.export_table_stats(ownname => &＃39;DANGHB&＃39;,tabname => &＃39;TEST_STAT&＃39;,stattab => &＃39;STAT_TABLE&＃39;);
PL/SQL procedure successfully completed
8.查看存放统计信息的表是否有内容：
SQL> select count(*) from stat_table;
COUNT(*)
----------
14
9.删除测试表的统计信息：
SQL> execute dbms_stats.delete_table_stats(ownname => &＃39;DANGHB&＃39;,tabname => &＃39;TEST_STAT&＃39;);
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=&＃39;TEST_STAT&＃39;;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT
11.导入统计信息：
SQL> execute dbms_stats.import_table_stats(ownname => &＃39;DANGHB&＃39;,tabname => &＃39;TEST_STAT&＃39;,stattab => &＃39;STAT_TABLE&＃39;);
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=&＃39;TEST_STAT&＃39;;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
TEST_STAT 67780 1044 2012/12/6 11:
--EOF
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有