PLSQL_统计信息系列07_数据字典统计信息的导出和导入

2014-05-05 Created By BaoXinjian

一、摘要

---

字典统计信息的导出和导入，用以备份dba_tab_statistics字典表中的统计信息

1. Export语法

DBMS_STATS.EXPORT_DICTIONARY_STATS (
    stattab VARCHAR2,  -- User statistics table identifier describing where to store the statistics
    statid VARCHAR2 DEFAULT NULL, -- Identifier (optional) to associate with these statistics within stattab
    statown VARCHAR2 DEFAULT NULL -- Schema containing stattab (if different from current schema)
);

2. Import语法

DBMS_STATS.IMPORT_DICTIONARY_STATS('STATS_DUMP','TEST1','MY_USER'); 

3. Fixed Table表统计信息的导出

DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab=>'STATS_DUMP', statown=>'MY_USER');

 

二、案例

---

1. 查看需要备份字典统计信息的目录

SELECT   owner, table_name, last_analyzed
  FROM   dba_tab_statistics
 WHERE   table_name NOT LIKE 'X$%' AND owner IN ('SYS', 'SYSTEM')
   AND   table_name = 'AUDIT$'

 

2. 创建存放统计信息的备份表

BEGIN
 DBMS_STATS.CREATE_STAT_TABLE('SCOTT','STATS_DUMP');
END;

 

3. 将统计信息导入至备份表中

BEGIN 
 DBMS_STATS.EXPORT_DICTIONARY_STATS('STATS_DUMP','TEST1','SCOTT');
END;

 

4. 查看备份表中的资料

SELECT * FROM scott.stats_dump
WHERE c1='AUDIT$'

 

5. 比较字典表和备份表的数据情况

SQL> SELECT count(*) FROM dba_tab_statistics;
  COUNT(*)
----------
      4728

SQL> SELECT count(*) FROM scott.stats_dump
  2  ;
  COUNT(*)
----------
     64691

 

6. 在字典表中AUDIT$中的数据

 

7. 在备份表STAT_DUMP中的数据

 

 

Thanks and Regards