oracle 分区01688,不能生成新快照 - Oracle数据库管理 - ITPUB论坛－中国专业的IT技术社区...

AIX 5.3

11.1.0.7

sysaux快满了(一共3G)，其中AWR的信息占了1.5G左右

alert 中：

Wed Sep 08 09:00:47 2010

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                 tablespace SYSAUX

MMON Flush encountered SYSAUX out of space error(1688).

MMON (emergency) purge of WR snapshots (15357) and older

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                 tablespace SYSAUX

MMON Flush encountered SYSAUX out of space error(1688).

MMON (emergency) purge of WR snapshots (15358) and older

Wed Sep 08 09:01:05 2010

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096 in                 tablespace SYSAUX

$

所以想删除一部快照

但奇怪的是

SQL> select count(*) from dba_hist_snapshot;

COUNT(*)

----------

0

怎么能没有快照呢，我手工生成快照又报错：

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); END;

*

ERROR at line 1:

ORA-13509: error encountered during updates to a AWR table

ORA-01688: unable to extend table ORA-01688: unable to extend table

SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1153364305_5522 by 4096

in tablespace SYSAUX

. partition  by  in tablespace

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 14

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 37

ORA-06512: at line 1

我又想把保留时间减小点

SQL> select * from dba_hist_wr_control;

DBID

----------

SNAP_INTERVAL

---------------------------------------------------------------------------

RETENTION

---------------------------------------------------------------------------

TOPNSQL

----------

1153364305

+00000 01:00:00.0

+00008 00:00:00.0

DEFAULT

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60);

BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>3*24*60); END;

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention

(259200)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 89

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137

ORA-06512: at line 1

在网上查了查，有篇文章说是跟baseline设置有关，可我这边还是没有记录

SQL> select count(*) from dba_hist_baseline;

COUNT(*)

----------

0

SQL>

真不知道oracle他在想什么呢，

各位大虾请指教一二

我现在想把AWR占用的空间腾出来先，SYSAUX满了毕竟不是什么好事

另外，这个还是挺奇怪的，是遇到BUG了吗，还是我哪里忽略了，怎么就不能生成快照了呢，以前的快照也没有，一个也没有

好晕。