Shulou(Shulou.com)06/01 Report--

Oracle statistical information recovery and backup, in response to this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

oracle 11g adds backup and restore capabilities for system information at the system level, user level, and table level! When we collect statistics on oracle system objects, in order to prevent the execution plan after the new statistics is not as good as the previous one, we can use

dbms_stats.export_(database/schema/table)_stats Export statistics to created tables

dbms_stats.import_(database/schema/table)_stats Import statistics into the system

To restore statistics!

Backup and recovery of statistical information includes the following levels

1)Database statistics backup recovery (can only be under sys user)

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)Backup and restore of statistics for schema (only under the schema owner's user, yang is the username)

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)Statistics backup restore for tables (only under users of 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');

Here's a test for the user level!

1 Create a table for statistics backup

yang@yangdb> exec dbms_stats.create_stat_table('yang','stat_4');

PL/SQL procedure successfully completed.

2 for user yang statistics, and statistical information exported to the table inside!

yang@yangdb> begin

2 dbms_stats.gather_schema_stats(

3 ownname => 'yang',

4 estimate_percent => 100,

5 method_opt => 'for all columns size auto',

6 degree => 2);

7 end;

8 /

PL/SQL procedure successfully completed.

yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';

TABLE_NAME LAST_ANALYZED

------------------------------ ------------------

T2 27-FEB-12

yang@yangdb> exec dbms_stats.export_schema_stats('yang','stat_4');

PL/SQL procedure successfully completed.

3 Delete previous statistics and query validation

yang@yangdb> exec dbms_stats.delete_schema_stats('yang');

PL/SQL procedure successfully completed.

yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';

TABLE_NAME LAST_ANALYZED

------------------------------ ------------------

T2

4 Reimport statistics from previous backups

yang@yangdb> exec dbms_stats.import_schema_stats('yang','stat_4');

PL/SQL procedure successfully completed.

Query the statistical analysis time of t2 table under yang user!

yang@yangdb> select table_name,last_analyzed from user_tables where table_name='T2';

TABLE_NAME LAST_ANALYZED

------------------------------ ------------------

T2 27-FEB-12

Oracle statistics on the recovery and backup of the answer to the question shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.