Oracle统计信息的备份

下面以Oracle9i 9.2.0 版本为例，讲解下统计信息的备份与恢复：

step1： 执行dbms包下面的create_stat_table函数， 创建统计信息的存储表

exec dbms_stats.create_stat_table(ownname => 'schema的名称',stattab => '存储备份信息的table名称');

 

 

step2：生成“将备份信息导出到存储备份信息的table中”的SQL，我只备份空间大于等于2兆的表（有点拗口，说白了就是：我要生成一些SQL语句，这些SQL的目的是将备份信息备份到存储表，即step1中创建的表，姑且叫tab_statics_bak）

 

select 'exec dbms_stats.EXPORT_TABLE_STATS(OWNNAME=>' || '''' || upper(owner) || ''',statown=>'''tab_statics_bak的schema名称'',TABNAME=>' || '''' || upper(table_name) || ''',STATTAB=>''tab_statics_bak'',STATID=>''备份标识'');' as script_for_export_stat

   from dba_tables

  where last_analyzed is not null

    and owner in (‘要备份的那些表的schema’)

    and last_analyzed > to_date('2010-01-01', 'yyyy-mm-dd')

    and table_name in

        (select tablename

           from (select a.segment_name as tablename,

                        round(a.bytes / 1024 / 1024, 2) as tablesize

                   from dba_segments a, dba_tables b

                  where a.segment_name = b.table_name

                    and b.owner in ('GLOBAL', 'GSZJ')

                    and b.last_analyzed > to_date('2010-01-01', 'yyyy-mm-dd'))

          where tablesize >= 2)

  order by owner;

 

step3： 生成“还原备份的统计信息”的SQL，只还原step2备份的统计信息

 

select 'exec dbms_stats.IMPORT_TABLE_STATS(OWNNAME=>' || '''' || upper(owner) || ''',statown=>''tab_statics_bak的schema'',TABNAME=>' || '''' || upper(table_name) ||

       ''',STATTAB=>''tab_statics_bak'',STATID=>''step2中的备份标识'');' as script_for_import_stat

  from dba_tables

 where last_analyzed is not null

   and owner in (‘要备份的那些表的schema’)   and table_name in

       (select tablename

          from (select a.segment_name as tablename,

                       round(a.bytes / 1024 / 1024, 2) as tablesize

                  from dba_segments a, dba_tables b

                 where a.segment_name = b.table_name

                   and b.owner in(‘要备份的那些表的schema’)  and b.last_analyzed > to_date('2010-01-01', 'yyyy-mm-dd'))  where tablesize >= 2);

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26566546/viewspace-714407/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26566546/viewspace-714407/