Oracle 11g 清理SYSAUX空间

朴灿烈づ我的快乐病毒、 2022-07-13 01:47 455阅读 0赞

SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等。今天就碰到了这个问题，数据库较慢，奇怪的是无法获取AWR报告。

SQL> select \* from v$version;

BANNER  
\--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
PL/SQL Release 11.2.0.4.0 - Production  
CORE    11.2.0.4.0      Production  
TNS for Linux: Version 11.2.0.4.0 - Production  
NLSRTL Version 11.2.0.4.0 - Production

**--截取获取数据库报告的片段，正常是显示快照id**

输入 num\_days 的值:  1  
Listing the last 1 days of Completed Snapshots  
**--手工生成快照保存，很明显是表空间不足**  
SQL> exec dbms\_workload\_repository.create\_snapshot();  
BEGIN dbms\_workload\_repository.create\_snapshot(); END;  
\*  
第 1 行出现错误:  
ORA-13509: 更新 AWR 表时出错  
ORA-01683: 索引 ORA-01683: 索引 SYS.WRH$\_ACTIVE\_SESSION\_HISTORY\_PK 分区 WRH$\_ACTIVE\_1148453265\_0 无法通过 8192 (在表空间 SYSAUX 中) 扩展  
. 分区  无法通过  (在表空间  中) 扩展  
ORA-06512: 在 "SYS.DBMS\_WORKLOAD\_REPOSITORY", line 99  
ORA-06512: 在 "SYS.DBMS\_WORKLOAD\_REPOSITORY", line 122  
ORA-06512: 在 line 1  
**--查询SYSAUX表空间的使用情况，消耗37G，快满了**  
SQL> SELECT Upper(F.TABLESPACE\_NAME)         "表空间名",  
           D.TOT\_GROOTTE\_MB                 "表空间大小(M)",  
           D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)",  
           To\_char(Round(( D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES ) / D.TOT\_GROOTTE\_MB \* 100, 2), '990.99')  
           || '%'                           "使用比",  
           F.TOTAL\_BYTES                    "空闲空间(M)",  
           F.MAX\_BYTES                      "最大块(M)"  
    FROM   (SELECT TABLESPACE\_NAME,  
                   Round(Sum(BYTES) / ( 1024 \* 1024 ), 2) TOTAL\_BYTES,  
                   Round(Max(BYTES) / ( 1024 \* 1024 ), 2) MAX\_BYTES  
            FROM   SYS.DBA\_FREE\_SPACE  
            GROUP  BY TABLESPACE\_NAME) F,  
           (SELECT DD.TABLESPACE\_NAME,  
                   Round(Sum(DD.BYTES) / ( 1024 \* 1024 ), 2) TOT\_GROOTTE\_MB  
            FROM   SYS.DBA\_DATA\_FILES DD  
            GROUP  BY DD.TABLESPACE\_NAME) D  
    WHERE  D.TABLESPACE\_NAME = F.TABLESPACE\_NAME  
    and D.tablespace\_name = 'SYSAUX';  
表空间名  表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)  最大块(M)  
\--------- ------------- ------------- -------- ----------- ----------  
SYSAUX         37887.98       37865.6   99.94%       22.38          1  
**--查看SYSAUX表空间表的使用情况**  
SQL> select \*  
       from (select segment\_name,  
                    segment\_type,  
                    bytes / 1024 / 1024  
               from dba\_segments  
              where tablespace\_name = 'SYSAUX'  
              and bytes / 1024 / 1024 >1000  
              order by bytes desc);  
SEGMENT\_NAME                          SEGMENT\_TYPE       BYTES/1024/1024  
\------------------------------------- ------------------ ---------------  
WRH$\_ACTIVE\_SESSION\_HISTORY           TABLE PARTITION       13479  
I\_WRI$\_OPTSTAT\_H\_OBJ\#\_ICOL\#\_ST        INDEX                 2590  
WRI$\_OPTSTAT\_HISTGRM\_HISTORY          TABLE                 2242  
WRH$\_EVENT\_HISTOGRAM\_PK               INDEX PARTITION       1856  
WRH$\_EVENT\_HISTOGRAM                  TABLE PARTITION       1792  
I\_WRI$\_OPTSTAT\_H\_ST                   INDEX                 1544  
WRH$\_ACTIVE\_SESSION\_HISTORY\_PK        INDEX PARTITION       1472  
WRH$\_LATCH                            TABLE PARTITION       1155

**--使用dbms\_workload\_repository.drop\_snapshot\_range可以删除历史数据，怎奈太慢了，半个小时完全没有反映。通过v$session看到执行的SQL是delete，这种做法无法降低高水位线。**

SQL> select min(snap\_id),max(snap\_id) from dba\_hist\_snapshot;  
MIN(SNAP\_ID) MAX(SNAP\_ID)  
\------------ ------------  
SQL> select min(snap\_id),max(snap\_id) from dba\_hist\_active\_sess\_history;  
MIN(SNAP\_ID) MAX(SNAP\_ID)  
\------------ ------------  
1             36768  
SQL> begin   
     dbms\_workload\_repository.drop\_snapshot\_range(   
      low\_snap\_id => 1,   
      high\_snap\_id => 36768,   
      dbid => 1148453265);   
end;

**--手工生成truncate，需要在SYS下执行**

select distinct 'truncate  table  '||segment\_name||';',s.bytes/1024/1024  
  from dba\_segments s  
 where s.segment\_name like 'WRH$%'  
   and segment\_type in ('TABLE PARTITION', 'TABLE')  
   and s.bytes/1024/1024>100  
   order by s.bytes/1024/1024/1024 desc;  
     
**--执行完成后，看效果**  
SQL> SELECT Upper(F.TABLESPACE\_NAME)         "表空间名",  
          D.TOT\_GROOTTE\_MB                 "表空间大小(M)",  
          D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)",  
          To\_char(Round(( D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES ) / D.TOT\_GROOTTE\_MB \* 100, 2), '990.99')  
          || '%'                           "使用比",  
          F.TOTAL\_BYTES                    "空闲空间(M)",  
          F.MAX\_BYTES                      "最大块(M)"  
   FROM   (SELECT TABLESPACE\_NAME,  
                  Round(Sum(BYTES) / ( 1024 \* 1024 ), 2) TOTAL\_BYTES,  
                  Round(Max(BYTES) / ( 1024 \* 1024 ), 2) MAX\_BYTES  
           FROM   SYS.DBA\_FREE\_SPACE  
           GROUP  BY TABLESPACE\_NAME) F,  
          (SELECT DD.TABLESPACE\_NAME,  
                  Round(Sum(DD.BYTES) / ( 1024 \* 1024 ), 2) TOT\_GROOTTE\_MB  
           FROM   SYS.DBA\_DATA\_FILES DD  
           GROUP  BY DD.TABLESPACE\_NAME) D  
   WHERE  D.TABLESPACE\_NAME = F.TABLESPACE\_NAME  
   and D.tablespace\_name = 'SYSAUX';  
表空间名  表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)  最大块(M)  
\--------- ------------- ------------- -------- ----------- ----------  
SYSAUX         37887.98       9132.67   24.10%    28755.31        544