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
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
