修改awr出现ORA-13541

在修改awr保留时间的时候出现了如下错误第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL               RETENTION            TOPNSQL
---------- -----------------------  -------------------  ----------

1279946125 +00000 00:30:00.0          +00030 00:00:00.0      30

因为之前更改过一次所以我想调整回原来的
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings (     interval => 60,
  3                                                             retention => 10080,
  4                                                             topnsql   =>100
  5                                                        );
  6  end;
  7  /
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 2

SQL> !oerr ora 13541
13541, 00000, "system moving window baseline size (%s) greater than retention (%s)"
// *Cause:  The system moving window baseline size must be less than the
//          retention setting.  The specified window size or retention
//          violate this.
// *Action: Check the moving window baseline size or retention.

SQL> select 691200/60/60/24 from dual;

691200/60/60/24
---------------
              8

SQL> select 604800/60/60/24 from dual;

604800/60/60/24
---------------
              7

SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME             BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------- ------------- ------------------
1279946125 SYSTEM_MOVING_WINDOW      MOVING_WINDOW                  8
由于MOVING_WINDOW_SIZE大于retention所以修改不成功。修改MOVING_WINDOW_SIZE大小
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL 过程已成功完成。

SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings(
  3                                     interval =>60,
  4                                     retention =>10080,
  5                                     topnsql   => 100
  6                                                     );
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL               RETENTION            TOPNSQL
---------- -----------------------  -------------------  ----------

1279946125 +00000 00:60:00.0          +00007 00:00:00.0      100

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

转载于:http://blog.itpub.net/22779291/viewspace-697003/