Backup Restore Table Schema Statistics

Applies to: Database Management Software Oracle 8 to Oracle 11g R2 all versions

Here we will apply backup & Restore of Table stats on EMP table of scott user:

SQL> select table_name,NUM_ROWS,LAST_ANALYZED from user_tables where table_name like 'EMP';

TABLE_NAME              NUM_ROWS LAST_ANAL
------------------------------ ----------          ---------                            
EMP                                14                    18-JAN-13

Create a table to store stats:

SQL> exec dbms_stats.create_stat_table(user,'stats_table1');

PL/SQL procedure successfully completed.

Export EMP table stats to STATS_TABLE1

SQL> exec dbms_stats.export_table_stats(user,'EMP',NULL,'STATS_TABLE1');

PL/SQL procedure successfully completed.

Cross check stats available in stats_table1:

SQL> select distinct c1 from stats_table1 where type='T';

C1
------------------------------                                                 
EMP

Delete existing table stats

SQL> exec dbms_stats.delete_table_stats('scott','emp');

PL/SQL procedure successfully completed.

Cross check stats doesn't exists

SQL> select table_name,NUM_ROWS,LAST_ANALYZED from user_tables where table_name like 'EMP';
TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------                            
EMP

Import Table stats:

SQL> exec dbms_stats.import_table_stats(null,'emp',null,'stats_table1');

PL/SQL procedure successfully completed.

Now, stats are back again. Cross check using:

SQL> select table_name,NUM_ROWS,LAST_ANALYZED from user_tables where table_name like 'EMP';

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------                            
EMP                                    14 18-JAN-13

You can also use below queries for Export and import Schema stats:

SQL> exec dbms_stats.export_schema_stats(user,'stats_table1');

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.import_schema_stats(user,'stats_table1');

PL/SQL procedure successfully completed.                                                                       

1 comment:

  1. Pretty! This was an incredibly wonderful post. Thank you for supplying this info.


    my site :: green coffee

    ReplyDelete