SQL> create table t1 as select * from dba_objects;
Table created
SQL> create table t2 as select * from dba_objects;
Table created
Table created
SQL> create table t2 as select * from dba_objects;
Table created
SQL> select (select count(*) from t1) t1,(select count(*) from t2) t2 from dual;
T1 T2
---------- ----------
49745 49746
T1 T2
---------- ----------
49745 49746
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
SQL> exec dbms_stats.gather_table_stats('admin','t1');
PL/SQL procedure successfully completed
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats('admin','t2');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
49745 704 2012/9/24 10:
49746 704 2012/9/24 10:
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
49745 704 2012/9/24 10:
49746 704 2012/9/24 10:
------创建存放统计信息的表t_stat
SQL> conn sys/oracle@remote_2003 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> conn sys/oracle@remote_2003 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> exec dbms_stats.create_t_statle('sys','t_stat');
PL/SQL procedure successfully completed
SQL> select count(*) from t_stat;
COUNT(*)
----------
0
------导出统计信息至t_stat表
SQL> exec dbms_stats.export_database_stats('t_stat');
PL/SQL procedure successfully completed
PL/SQL procedure successfully completed
SQL> select count(*) from t_stat;
COUNT(*)
----------
0
------导出统计信息至t_stat表
SQL> exec dbms_stats.export_database_stats('t_stat');
PL/SQL procedure successfully completed
SQL> select count(*) from t_stat;
COUNT(*)
----------
27744
------删除t1,t2统计信息
SQL> conn admin/admin@remote_2003
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as admin
SQL> exec dbms_stats.delete_table_stats('admin','t1');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.delete_table_stats('admin','t2');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
PL/SQL procedure successfully completed
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
SQL> conn sys/oracle@remote_2003 as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> exec dbms_stats.import_database_stats('t_stat');
PL/SQL procedure successfully completed
SQL> conn
admin/admin@remote_2003
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as admin
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
49745 704 2012/9/24 10:
49746 704 2012/9/24 10:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as admin
SQL> select NUM_ROWS,BLOCKS,LAST_ANALYZED from user_tables where table_name in ('T1','T2');
NUM_ROWS BLOCKS LAST_ANALYZED
---------- ---------- -------------
49745 704 2012/9/24 10:
49746 704 2012/9/24 10:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26839123/viewspace-745070/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26839123/viewspace-745070/