Oracle11g 生成手动的快照报告报错：

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_1351169222_0 by 1024 in

tablespace SYSAUX

. partition  by  in tablespace

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

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

ORA-06512: at line 1

看报错分析为SYSAUX表空间满了。

SQL> set linesize 400

SQL> set pagesize 400

SQL> select b.tablespace_name,

  2  round(b.TOTAL_GB) || 'G' as TOTAL_GB,

  3  round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2) || '%' FREE_PCT,

  4  (100 -round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2)) || '%' USED_PCT,

  5  round(nvl(a.FREE_GB,0),2) || 'G' as FILE_FREE_GB,

  6  round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))),2) || 'GB' TOTAL_FREE_GB

  7  from (select TABLESPACE_NAME, sum(bytes) / 1024 / 1024 / 1024 FREE_GB

  8   from dba_FREE_space

  9  group by tablespace_name) a,

 10  (select tablespace_name,

 11  SUM(BYTES) / 1024 / 1024 /1024 DISK_GB,

 12  sum(decode(maxbytes, 0, bytes, maxbytes)) / 1024 / 1024 / 1024 as TOTAL_GB

 13  FROM DBA_DATA_FILES

 14  group by tablespace_name) b

 15  where a.tablespace_name(+) = b.tablespace_name

 16  order by 1;

查询表空间使用情况。

可以看到SYSAUX表空间满了，增加SYSAUX表空间。

SQL> ALTER DATABASE DATAFILE '/data/oracle/oradata/WMSPROD/sysaux01.dbf' RESIZE 22767M;

Database altered.

可以 生成手动的快照：

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

问题解决。