Import and export of oracle statistics

For more information about the import and export of oracle statistics, see the official oracle documentation. 1. database version: SQL> select * from v $ version 2/BANNER Release 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. create test table www.2cto.com SQL> create Table test_stat as select * from dba_objects; table created 3. check whether the test has statistical information: SQL> select. TABLE_NAME,. NUM_ROWS,. BLOCKS,. LAST_ANALYZED from dba_tables a where. table_name = 'test _ stat'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ---------- ----------------- TEST_STAT 4. no result in the previous step, so collect the statistical information: 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. view statistics again: SQL> select. TABLE_NAME,. NUM_ROWS,. BLOCKS,. LAST_ANALYZED from dba_tables a where. table_name = 'test _ stat'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ---------- ----------------- TEST_STAT 67780 1044 11: 6. create a TABLE for storing statistics: SQL> execute dbms_stats.create_stat_table (ownname => 'danghb', stattab => 'stat _ table'); PL/SQL procedure successfully completed 7. export Statistics: SQL> execute dbms_stats.export_table_stats (ownname => 'danghb', tabname => 'test _ stat', stattab => 'stat _ table '); PL/SQL procedure successfully completed 8. check whether the table storing statistics has content: SQL> select count (*) from stat_table; COUNT (*) -------- 14 9. delete the statistics of the TEST table: SQL> execute dbms_stats.delete_table_stats (ownname => 'danghb', tabname => 'test _ stat'); PL/SQL procedure successfully completed 10. delete: SQL> select. TABLE_NAME,. NUM_ROWS,. BLOCKS,. LAST_ANALYZED from dba_tables a where. table_name = 'test _ stat'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ---------- ----------------- TEST_STAT 11. import statistics: SQL> execute dbms_stats.import_table_stats (ownname => 'danghb', tabname => 'test _ stat', stattab => 'stat _ table '); PL/SQL procedure successfully completed 12. check whether the import is successful: SQL> select. TABLE_NAME,. NUM_ROWS,. BLOCKS,. LAST_ANALYZED from dba_tables a where. table_name = 'test _ stat'; TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ---------- ----------------- TEST_STAT 67780 1044 11: -- EOF