Oracle 11g has added the system information backup and recovery functions at the system level, user level, and table level! When we collect statistics on oracle system objects, we can use
Dbms_stats.export _ (database/schema/table) _ stats export statistics to the created table
Dbms_stats.import _ (database/schema/table) _ stats import statistics to the System
To restore statistics!
The backup and recovery of statistical information includes the following levels:
1) backup and recovery of database statistical information (only for sys Users)
Exec dbms_stats.create_stat_table ('sys ', 'stat _ sys ');
Exec dbms_stats.export_database_stats ('stat _ sys ');
Exec dbms_stats.import_database_stats ('stat _ sys ');
2) Statistical information backup and recovery of the solution (yang is the user name only under the user of the solution owner)
Exec dbms_stats.create_stat_table ('yang', 'stat _ 2 ');
Exec dbms_stats.export_schema_stats ('yang', 'stat _ 2 ');
Exec dbms_stats.import_schema_stats ('yang', 'stat _ 2 ');
3) backup and recovery of table statistical information (only under the user of the table owner)
Exec dbms_stats.create_stat_table ('yang', 'stat _ 3 ');
Exec dbms_stats.export_table_stats ('yang', 't1', null, 'stat _ 3 ');
Exec dbms_stats.import_table_stats ('yang', 't1', null, 'stat _ 3 ');
The following is a test for the user level!
1. Create a table for storing statistical information backup
Www.bkjia.com @ bkjia> exec dbms_stats.create_stat_table ('yang', 'stat _ 4 ');
PL/SQL procedure successfully completed.
2. collect statistics on user yang and export the statistics to the table!
Www.bkjia.com @ bkjia> begin
2 dbms_stats.gather_schema_stats (
3 ownname => 'yang ',
4 estimate_percent = & gt; 100,
5 method_opt => 'for all columns size auto ',
6 degree => 2 );
7 end;
8/
PL/SQL procedure successfully completed.
Www.bkjia.com @ bkjia> select table_name, last_analyzed from user_tables where table_name = 't2 ';
TABLE_NAME LAST_ANALYZED
------------------------------------------------
T2 27-FEB-12
Www.bkjia.com @ bkjia> exec dbms_stats.export_schema_stats ('yang', 'stat _ 4 ');
PL/SQL procedure successfully completed.
3. Delete the previous statistics and query and verify the statistics.
Www.bkjia.com @ bkjia> exec dbms_stats.delete_schema_stats ('yang ');
PL/SQL procedure successfully completed.
Www.bkjia.com @ bkjia> select table_name, last_analyzed from user_tables where table_name = 't2 ';
TABLE_NAME LAST_ANALYZED
------------------------------------------------
T2
4. re-import the previous backup statistics
Www.bkjia.com @ bkjia> exec dbms_stats.import_schema_stats ('yang', 'stat _ 4 ');
PL/SQL procedure successfully completed.
Query the statistical analysis time of table t2 under the yang user!
Www.bkjia.com @ bkjia> select table_name, last_analyzed from user_tables where table_name = 't2 ';
TABLE_NAME LAST_ANALYZED
------------------------------------------------
T2 27-FEB-12