ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (86400)

想通过 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 来修改AWR snapshot 保留的时间,但是调整的时候遇到ORA-13541错误
过程如下:
C:\Users\aaaa>sqlplus yang/yang as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 8月 4 20:51:28 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@rac1>col SNAP_INTERVAL for a20
sys@rac1>col RETENTION for a20
检查当前系统的保留时间为8天,1小时采样一次.
sys@rac1>select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
1284003062 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT
sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 24*60 );
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 24*60 ); END;
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (86400)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 1
这里 691200 ,86400都是以秒为单位的。
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.
sys@rac1>select 691200/60/60/24 from dual;
691200/60/60/24
---------------
              8
sys@rac1>select 86400/(24*24*60) from dual;
86400/(24*24*60)
----------------
             2.5
查看系统的当前的MOVING_WINDOW_SIZE
sys@rac1> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME          BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------        ------------- ------------------
1284003062 SYSTEM_MOVING_WINDOW   MOVING_WINDOW             8
修改其大小 为2 ,即两天。
sys@rac1> exec dbms_workload_repository.modify_baseline_window_size(2);
PL/SQL 过程已成功完成。

sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 5*24*60 );
PL/SQL 过程已成功完成。

sys@rac1>select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
1284003062 +00000 00:30:00.0    +00005 00:00:00.0    DEFAULT

sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 2*24*60 );
PL/SQL 过程已成功完成。

sys@rac1>select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
1284003062 +00000 00:30:00.0    +00002 00:00:00.0    DEFAULT

sys@rac1>
上一篇:智能交通助力城市道路行车提速15%


下一篇:【物联网智能网关-07】PCF8591 AD转换模块I2C通信实现