31 积分	0 好友	0 主题

发消息

请教怎么备份oracle的统计信息

1^#

发表于 2012-6-11 20:04:57 | 查看: 11181| 回复: 8

客户要求每周备份一次oracle 11g r2 rac统计信息，我想了解一下用什么命令可以最简单最直接地备份，另外这个统计信息有什么实际的意义吗？

[ 本帖最后由 516078670 于 2012-6-11 20:07 编辑 ]

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-6-11 20:19:30

如何备份oracle 的统计信息，可以通过 dbms_stats包将 statistics export到表中，之后将该统计表在exp为dmp 文件，以逻辑备份。

ODM DATA:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0    Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create user maclean identified by oracle;

User created.

SQL> grant dba to maclean;

Grant succeeded.

SQL> conn maclean/maclean
Connected.

SQL> exec dbms_stats.create_stat_table('MACLEAN','STATS');

PL/SQL procedure successfully completed.

在MACLEAN SCHEMA 下建立名为 STATS的表存放相关统计信息

SQL> exec dbms_stats.export_schema_stats('MACLEAN','STATS','MACLEAN1');

PL/SQL procedure successfully completed.

在导出 MACLEAN 模式下的统计信息到STATS表， statid为MACLEAN1 作为统计信息的标示符

SQL> Exec dbms_stats.export_dictionary_stats('STATS','MACLEAN1_DIC','MACLEAN');

PL/SQL procedure successfully completed.

导出字典统计信息到 MACLEAN.STATS表，  statid为MACLEAN1_DIC 作为统计信息的标示符

用exp 导出该stats表到dmp文件

[oracle@vrh8 ~]$ exp maclean/maclean tables=stats file=stats.dmp

Export: Release 10.2.0.5.0 - Production on Mon Jun 11 08:18:28 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                         STATS    59734 rows exported
Export terminated successfully without warnings.

回复只看该作者道具举报