因AWR表lob段空间不能回收导致SYSAUX表空间过大的处理

环境：

OS：RHEL A4U4 

DB：ORACLE RAC 10.2.0.3

问题分析 ：

通过查询相关视图发现在SYSAUX表空间中有一个非常大的LOB段，已经有5GB，这个LOB段存放的是AWR组件的一个WRH$_SQLTEXT表中SQL_TEXT栏位信息，这个组件是定期的收集数据库系统快照，用于对数据库状态进行情况记录和性能分析。

Metalink action：

High Storage Consumption for LOBs in SYSAUX Tablespace [ID 396502.1]，

以限制模式打开DB

SELECT * FROM DBA_LOBS WHERE SEGMENT_NAME = ‘SYS_LOB000008958C0004$$’;

SQL> select segment_name,index_name from dba_lobs where table_name='WRH$_SQLTEXT';

SEGMENT_NAME                   INDEX_NAME

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

SYS_LOB0000008957C00004$$      SYS_IL0000008957C00004$$

SQL> select bytes from dba_segments where tablespace_name='SYSAUX' and segment_name='SYS_LOB0000008957C00004$$';

BYTES

----------

231735296

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

PL/SQL procedure successfully completed.

SQL> alter table wrh$_sqltext move lob(SQL_TEXT) store as "SYS_LOB0000008957C00004$$" (tablespace SYSAUX);

Table altered.

注：此步骤需要有很大的SYSAUX空间。

SQL> select bytes from dba_segments where tablespace_name='SYSAUX' and segment_name='SYS_LOB0000008957C00004$$';

BYTES

----------

49283072

SQL> select index_name from dba_indexes where table_name='WRH$_SQLTEXT' and status='UNUSABLE';

INDEX_NAME

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

WRH$_SQLTEXT_PK

SQL> alter index WRH$_SQLTEXT_PK rebuild;

Index altered.

问题当前状态：已经关闭ARW快照的信息收集。

采取drop AWR repository的办法：

SQL> connect / as sysdba

SQL> @?/rdbms/admin/catnoawr.sql

SQL> @?/rdbms/admin/catawrtb.sql

重开instance：

SQL> shutdown immediate

SQL> startup