EVENT:

SOLUTION:

如果要回收sysaux表空间的awr信息，可以考虑下面步骤：

1）检查表wrh$_active_session_history中有多少个分区

SQL> select table_name,partition_name fromdba_tab_partitions

where table_name='WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME PARTITION_NAME
---------------------------------------------------------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_675
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_723
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_760
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_829
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_877
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_922
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_960
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_782247102_972
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN

9 rows selected.

【注】awr采样信息室保留在wrh$_active_session_history中。

2）尝试执行清除操作

SQL> alter session set "_swrf_test_action" =72;

如果执行成功，继续下一步，如果有有报错，按下面方法处理完成后再继续下一步：

ORA-02097: parameter cannot be modified because specifiedvalue is invalid
ORA-13509: error encountered during updates to a AWR table
ORA-14327: Some index [sub]partitions could not be rebuilt

解决上面错误方法为：

a) 查看udump的跟踪文件

*** KEWROCISTMTEXEC - encountered error: (ORA-14327: Someindex [sub]partitions could not be rebuilt
ORA-20001: Component "WRH$_SQLSTA_2446360981_11691" does not exist onindex "WRH$_SQLSTAT_INDEX" in schema "SYS".
ORA-06512: at "SYS.DBMS_I_INDEX_UTL", line 748
ORA-06512: at "SYS.DBMS_INDEX_UTL", line 397
ORA-06512: at "SYS.DBMS_INDEX_UTL", line 556
ORA-06512: at line 1
)
*** SQLSTR: total-len=228, dump-len=228,
STR={alter table WRH$_SQLSTAT split partition
WRH$_SQLSTA_2446360981_11691at (2446360981,12774) into (partition
WRH$_SQLSTA_2446
360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774
tablespace SYSAUX) update indexes}

执行sql语句如下：

sql>alter table WRH$_SQLSTAT split partitionWRH$_SQLSTA_2446360981_11691 at (2446360981,12774)
into (partition WRH$_SQLSTA_2446 360981_11691 tablespace SYSAUX, partitionWRH$_SQLSTA_2446360981_12774 tablespace SYSAUX)
update indexes;

3）执行下面的脚本查找出wrh$_active_session_history对应的snapid和dbid

set serveroutput on
declare
CURSOR cur_part IS
SELECT
partition_name from dba_tab_partitions
WHERE table_name =
'WRH$_ACTIVE_SESSION_HISTORY';query1 varchar2(200);
query2
varchar2(200);TYPE partrec IS RECORD (snapid number, dbid number);TYPE partlistIS TABLE OF partrec;

Outlist partlist;
begin

dbms_output.put_line('PARTITION NAME SNAP_ID DBID');

dbms_output.put_line('--------------------------------');

for part in cur_part loop
query1 := 'select min(snap_id), dbid from
sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by
dbid';
execute immediate query1 bulk collect into OutList;

if
OutList.count > 0 then
for i in OutList.first..OutList.last loop

dbms_output.put_line(part.partition_name||' Min'||OutList(i).snapid||'
'||OutList(i).dbid);
end loop;
end if;

query2 := 'select
max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition
('||part.partition_name||') group by dbid';
execute immediate query2 bulk
collect into OutList;

if OutList.count > 0 then
for i in
OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||'
Max '||OutList(i).snapid||' '||OutList(i).dbid);

dbms_output.put_line('---');
end loop;
end if;

end loop;

end;
/

上面代码执行后会出现类似界面：

PARTITION NAME SNAP_ID DBID
--------------------------- ------- ----------
WRH$_ACTIVE_782247102_627 Min 627 782247102
WRH$_ACTIVE_782247102_627 Max 674 782247102
---
WRH$_ACTIVE_782247102_675 Min 675 782247102
WRH$_ACTIVE_782247102_675 Max 722 782247102
---
WRH$_ACTIVE_782247102_723 Min 723 782247102
WRH$_ACTIVE_782247102_723 Max 759 782247102
---
WRH$_ACTIVE_782247102_760 Min 760 782247102
WRH$_ACTIVE_782247102_760 Max 828 782247102
---
WRH$_ACTIVE_782247102_829 Min 829 782247102
WRH$_ACTIVE_782247102_829 Max 876 782247102
---
WRH$_ACTIVE_782247102_877 Min 877 782247102
WRH$_ACTIVE_782247102_877 Max 921 782247102
---

PL/SQL procedure successfully completed.

4）删除指定的awr信息：

[bash]

SQL> begin
2 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
3 675,
4 722,
5 782247102);
6 end;
7 /

[/bash]

到此为止轻松回收sysaux空间。

参考文档：Doc ID:387914.1