SYSAUX表空间占用过大情况下的处理（AWR信息过多）

2024-02-15 08:43:04

SYSAUX表空间占用过大情况下的处理（AWR信息过多）

真题1、 SYSTEM和SYSAUX表空间存储的内容有哪些区别？若SYSAUX表空间占用过大则应该如何处理？

答案：在一般情况下，企业产生的业务数据应该存放在单独的数据表空间，而不应该使用系统已存在的表空间，尤其不能将业务数据保存到SYSTEM和SYSAUX表空间中，所以，DBA需要着重关注SYSTEM和SYSAUX表空间的占用情况。

Oracle服务器使用SYSTEM表空间管理整个数据库。这个表空间包含系统的数据字典和关于数据库的管理信息，这些信息均包含在SYS方案中，只有SYS用户或者拥有所需权限的其它管理用户才可访问这些信息。SYSTEM表空间用于核心功能（例如数据字典表）。

SYSAUX是SYSTEM表空间的辅助表空间。Oracle DB早期版本中某些使用SYSTEM表空间或其本身表空间的组件和产品现在改为使用SYSAUX表空间。每个Oracle Database 10g（或更高版本）数据库都必须拥有SYSAUX表空间。辅助表空间SYSAUX用于附加的数据库组件，例如，OEM库（Oracle Enterprise Manager Repository）、AWR快照信息库、统计信息、审计信息等。

SYSTEM和SYSAUX表空间是在创建数据库时创建的必需存在的表空间。这些表空间必须联机。在OPEN状态下，SYSAUX表空间可以脱机以执行表空间恢复，而SYSTEM表空间则不能，这两种表空间都不能设置为只读状态。在MOUNT状态下，任何表空间都可以脱机。

SYSTEM表空间的大小一般变化不大，而SYSAUX表空间在默认条件下如果不做任何配置，那么随着时间的推移，会越来越大。所以，如果SYSAUX表空间过大，那么应该及时诊断清理该表空间。

对于SYSTEM表空间而言，如果占用过大，那么一般情况下是由于审计表（SYS.AUD$）过大引起的。需要将审计表移动到其它表空间中，然后再清理审计表（TRUNCATE TABLE SYS.AUD$）即可。需要注意的是，如果审计表过大，那么应该分部去清理审计表，详细步骤可以参考审计部分。

对于SYSAUX表空间而言，如果占用过大，那么一般情况下是由于AWR信息或对象统计信息没有及时清理引起的，具体原因可以通过如下的SQL语句查询：

SELECT OCCUPANT_NAME "Item",

SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",

SCHEMA_NAME "Schema",

MOVE_PROCEDURE "Move Procedure"

FROM V$SYSAUX_OCCUPANTS

WHERE SPACE_USAGE_KBYTES > 1048576

ORDER BY "Space Used (GB)" DESC;

如果OCCUPANT_NAME列为SM/AWR（Server Manageability - Automatic Workload Repository），那么表示AWR信息占用过大；如果该列为SM/OPTSTAT（Server Manageability - Optimizer Statistics History），那么表示优化器统计信息占用过大。

也可以直接查询DBA_SEGMENTS视图获取信息：

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_M

FROM DBA_SEGMENTS D

WHERE D.TABLESPACE_NAME = 'SYSAUX'

GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE

ORDER BY SIZE_M DESC;

然后查询占用空间较大的表，即可得到占用空间较大的原因，下面分别讨论。

（一）AWR信息占用过大

如果确认是AWR信息占用空间过大，那么还可以使用如下的SQL脚本获取AWR占用信息的详细信息：

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

如果AWR信息占用过大，那么可以通过设置AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：

SELECT * FROM DBA_HIST_WR_CONTROL;

通过如下的SQL语句可以设置AWR信息的保留时间为7天（7*24*60），每隔1小时收集一次AWR信息：

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>7*24*60);

需要注意的是，在Oracle 10g中，AWR默认保留7天，在Oracle 11g中，AWR默认保留8天。

在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

LOW_SNAP_ID => 1,

HIGH_SNAP_ID => 36768,

DBID => 1148453265);

END;

如果DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE执行太慢，那么可以先执行TRUNCATE操作：

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;

执行完TRUNCATE操作后，再执行DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE。需要注意的是，以上TRUNCATE操作会将AWR中的所有信息全部清除。所以，需要先确认释放需要这些AWR信息，当然也可以先把需要的AWR信息做导出操作，然后再清空以上AWR信息。

需要注意的是，DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放SYSAUX表空间。此时，应该对相关的表执行MOVE或TRUNCATE操作。在执行MOVE操作时，由于AWR信息的表都是分区表，不能对分区表全表执行MOVE操作，所以需要单独对分区执行MOVE操作，例如：

ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION 分区名称;

执行完MOVE操作后，需要对索引进行重建。同理，对于分区索引，只能对分区的单个索引进行重建，而不能总体重建：

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分区名称;

需要注意的是，可以在以上SQL后加上“UPDATE GLOBAL INDEXES”子句让全局索引不失效。

（二）统计信息占用过大

如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL; --查询统计信息的保留时间

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7); --设置统计信息的保留时间

若发现统计信息占用了SYSAUX上的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理。

以下的SQL语句对于诊断SYSAUX表空间的占用情况非常有用：

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

以下SQL可以查询到无效的ASH信息：

SELECT COUNT(*)

FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A

WHERE NOT EXISTS (SELECT 1

FROM SYS.WRM$_SNAPSHOT B

WHERE A.SNAP_ID = B.SNAP_ID

AND A.DBID = B.DBID

AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);

最后需要说明的一点是，负责收集和清理AWR信息的后台进程为MMON，而隐含参数“_swrf_test_action”可以调试MMON的行为，可以和10046事件结合使用。MMON进程每分钟都会自动刷新一定的AWR数据到磁盘上，默认情况下，MMON每30分钟做一次AWR信息的清理工作。在trace文件中可以看到“MMON Auto-Purge cycle”字样。

& 说明：

有关SYSTEM和SYSAUX的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152868/

有关审计的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140644/

有关数据库操作导致索引失效的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152871/

一般来讲除开业务数据存放的表空间，DBA要着重关注SYSTEM,SYSAUX,UNDO,TEMP表空间，SYSTEM表空间的大小一般是衡定的，UNDO和TEMP表空间的大小由数据库的业务情况决定，而SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大！SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放 EM相关的内容以及表统计信息，AWR快照，审计信息等，个人认为，如果你的SYSAUX表空间大小超过2G，那么该考虑让他减肥了！

查询语句：

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_M

FROM DBA_SEGMENTS D

WHERE D.TABLESPACE_NAME = 'SYSAUX'

GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE

ORDER BY SIZE_M DESC

查询出来基本都是AWR的数据，查询AWR的保留时间：

SELECT * FROM Dba_Hist_Wr_Control;

发现该库保留了180天约半年的时间，商量之后将该时间缩短至30天：

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

一：使用下列语句查询表空间使用率
SELECT * FROM (
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');

二：查询SYSAUX表空间内各个分类项目占存储空间的比重，很明显可以看出来AWR快照占用了2G左右的空间，统计信息为149M左右，同时数据库关闭了审计audit_trail，所以审计表aud$不占空间
SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1

三：修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除

SQL> select dbms_stats.get_stats_history_retention from dual; 
 
GET_STATS_HISTORY_RETENTION 
--------------------------- 
                         31 
 
SQL> exec dbms_stats.alter_stats_history_retention(7);       
PL/SQL procedure successfully completed. 
 
SQL> select dbms_stats.get_stats_history_retention from dual; 
 
GET_STATS_HISTORY_RETENTION 
--------------------------- 
                          7

四：修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改

SQL> begin 
         dbms_workload_repository.modify_snapshot_settings ( 
            interval => 60, 
            retention => 10080, 
            topnsql => 100 
          ); 
end;

五：删除AWR快照，再次查看SYSAUX表空间使用率,最后表空间使用率降低为38.42%

select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID

begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10758,
high_snap_id => 10900,
dbid => 387090299);
end;

以下脚本可以用于诊断SYSAUX表空间使用情况

./opatch lsinventory -detail

@?/rdbms/admin/awrinfo

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_tab_history;

select count(*) from sys.wri$_optstat_ind_history;

select count(*) from sys.wri$_optstat_histhead_history;

select count(*) from sys.wri$_optstat_histgrm_history;

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

---sysaux表空间占用率很高是awr太多？

select * from dba_hist_wr_control;

---30分钟收集一次，并保留8天时间(单位：分钟) interval设置0 表示关闭捕获快照

exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>8*24*60);

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

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

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

ORA-06512: at line 1

----查看移动窗口基线大小 MOVING_WINDOW_SIZE=8

select * from dba_hist_baseline;

---修改移动窗口基线大小

exec dbms_workload_repository.modify_baseline_window_size(5);

----再执行就不报错了

exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>5*24*60);

---手动创建一个快照

exec dbms_workload_repository.create_snapshot();

----创建baseline

exec dbms_workload_repository.create_baseline(56,59,'solgle-db-1');

ORA-13506: operation failed due to invalid snapshot range (56, 59)

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

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

ORA-06512: at line 1

----查看所有快照

select * from sys.wrh$_active_session_history order by sample_time desc;

----以某段快照为例，创建基线

exec dbms_workload_repository.create_baseline(2240,2246,'solgle-db-1');

---手工删除某阶段快照

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>2248,high_snap_id=>2249,dbid=>1369758280);

----删除baseline

exec dbms_workload_repository.drop_baseline(baseline_name=>'solgle-db-1',cascade=>true);

若发现statistics统计信息占用了SYSAUX上的大量空间，则可以考虑使用dbms_stats.purge_stats过程实施清理

Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.

2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”

3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.

ACTION PLAN:
=========

A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)

SQL> SPOOL CHECK1.OUT

1) Try to force the execution of the purge operations :

SQL> alter session set “_swrf_test_action” = 72;

2) Purging snapshots :

SQL> exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

3) Then again execute the below set of SQL’s and upload the spool output file

SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SQL> SPOOL OFF

B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);

NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes

C) Check the value of STATISTICS_LEVEL

If the above value is Set to ALL, then consider changing to TYPICAL.

The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

Once the above actions performed, please keep us posted on the status of the same

Oracle 11g 清理SYSAUX空间

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

AWR不自动删除导致SYSAUX表空间满

不知何时何原因，一个数据库的AWR不自动删除了，然后慢慢的SYSAUX表空间满了，alert日志也开始报警

其主要是WRH$_ACTIVE_SESSION_HISTORY 的一个分区表占了很多，没遇到过的问题，总是感觉很紧张，尤其又是生产库

于是乎，想了个笨方法开始手动删WRH$_ACTIVE_SESSION_HISTORY的数据，删的差不多了就开始收缩表，收缩完表空间有地了

可是过段时间又满了，心想这笨方法可不行，开始找方法

在官方发现文档 387914.1就是说的这个问题

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)

In this Document

Symptoms

Cause

Solution

Community Discussions

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.

CAUSE

Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed, then the partition won't be dropped and as such the table will contain old data.

If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.

SOLUTION

A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:

alter session set "_swrf_test_action" = 72;

To perform a single split of all the AWR partitions.

Check the partition details for the offending table before the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
Split the partitions so that there is more chance of the smaller partition being purged:
alter session set "_swrf_test_action" = 72;

NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.
Check the partition details for the offending table after the split:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

With smaller partitions it is expected that some will be automatically removed when the retention period of all the rows within each partition is reached.

As an alternative, you could purge data based upon a snapshot range. Depending on the snapshots chosen, this may remove data that has not yet reached the retention limit so this may not be suitable for all cases.

The following output shows the min and max snapshot_id in each partition.

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 partlist IS 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;
/

Once you have split the partitions and identified a partition with a range of snap ids that can be deleted, you can free up the memory by dropping a snapshot range than matches the high and low snap_ids for the partition:

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);

其方法就是执行alter session set "_swrf_test_action" = 72; 让AWR相关的分区表都创建一个新的分区，等待老分区的数据过期，oracle就会自动删除。

可眼下表空间已经满了，等不到老分区的数据过期，可咋办

那么可以先修改AWR的有效保留期，比如改成5天。等5天后oracle删掉分区在把保留期改回来。如果老数据需要保留可以导出AWR的信息。

流言终结者: AWR的保留天数和SYSAUX表空间的使用率有关吗？

今天在QQ群的技术讨论中有人提及AWR实际保留的天数并非10g的 7天或 11g 的 8天，而是视乎SYSAUX表空间的使用率而定，当SYSAUX表空间空闲空间较多时会将AWR数据保留地更久。

虽然不知道以上这番理论出自那部书籍，但是至少是说的有模有样的，而且网友还告诉我这是他测试过的结果。

实际是这样吗？

我相信这位网友并没有吹牛，他很可能查询dba_hist_snapshot等AWR视图且看到了的确有7天之前的快照仍被保留着，而没有被清理掉。我们来重演他所看到的现场:

测试使用版本11.2.0.2 ， 11g中默认AWR保留8天：

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------------------------------
www.askmaclean.com & www.askmaclean.com

SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select snap_interval,retention from dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------- --------------------
+00000 01:00:00.0    +00008 00:00:00.0

以上确认了默认的快照间隔为1小时 ，且保留时间为8天

检查当前SYSAUX表空间的使用率

REM tablespace report

set linesize 200

select a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes / 1048576) Max
  from (select f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
          from dba_data_files f
         group by tablespace_name) a,
       (select f.tablespace_name, sum(f.bytes) bytes_free
          from dba_free_space f
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 -
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
       sys.v_$Temp_extent_pool  p,
       dba_temp_files           f
 where p.file_id(+) = h.file_id
   and p.tablespace_name(+) = h.tablespace_name
   and f.file_id = h.file_id
   and f.tablespace_name = h.tablespace_name
 group by h.tablespace_name
 ORDER BY 1
/

TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MGMT_AD4J_TS                          200        199          1         99          1      32768
MGMT_ECM_DEPOT_TS                      40         13         27         32         68      32768
MGMT_TABLESPACE                      1350         86       1265          6         94      32768
SYSAUX                                600        295        305         49         51      32768
SYSTEM                                700        231        469         33         67      32768
TEMP                                   21         21          0        100          0      32768
UNDOTBS1                              495        358        137         72         28      32768
USERS                                1950       1243        707         64         36      32768

SYSAUX表空间剩余295MB空间，空闲率较高

因为这套数据库在2011-10-17之后就一直没有打开过，所以Automatic Workload Repository中最早保留的快照信息是在2011-10-10，通过查询dba_hist_snapshot视图可以反映这一点:

select snap_id,
       to_char(begin_interval_time, 'YYYY-MM-DD'),
       to_char(end_interval_time, 'YYYY-MM-DD')
  from dba_hist_snapshot
 order by snap_id;
   SNAP_ID TO_CHAR(BE TO_CHAR(EN
---------- ---------- ----------
        96 2011-10-10 2011-10-10
        97 2011-10-10 2011-10-10
        98 2011-10-10 2011-10-10
        99 2011-10-10 2011-10-10
       100 2011-10-10 2011-10-10
       101 2011-10-10 2011-10-11
       102 2011-10-11 2011-10-11
       103 2011-10-11 2011-10-11
...................
       221 2011-10-17 2011-10-17
       222 2011-10-24 2011-10-24

SQL> select sysdate from dual;

SYSDATE
---------
24-OCT-11

当前的日期是24-OCT-11，而最早的快照信息是在2011-10-10，这样就达成了网友所说的AWR的保留时间并非7或8天，”awr保留天数根据sysaux大小决定。” 或 “默认7天，sysaux足够大这个7天没有意义” 的说法。

事实是这样吗？

不是的!

那么为什么能看到早于7天的快照呢？

回答：不要被所看到的信息所蒙蔽，虽然我们常说事实胜于雄辩或实践是检验真知的唯一，但事情的表象往往会欺骗我们。

以上这个问题的关键点并非在于是否能看到早于7天的snapshot快照信息，而在于当MMON后台进程(该进程负责收集和清理AWR数据)在执行对过期快照清理工作时是否会清除7 或 8 天之前的snapshot，以及MMON后台进程多久才Purge一次AWR Snapshot。

以上这些问题，我们可以通过_swrf_test_action参数和10046 trace搞清楚:

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3   WHERE x.inst_id = USERENV ('Instance')
  4   AND y.inst_id = USERENV ('Instance')
  5   AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%_swrf%';

NAME                 VALUE      DESCRIB
-------------------- ---------- --------------------------------------------------
_swrf_test_action    0          test action parameter for SWRF
_swrf_mmon_flush     TRUE       Enable/disable SWRF MMON FLushing
_swrf_mmon_metrics   TRUE       Enable/disable SWRF MMON Metrics Collection
_swrf_metric_frequen FALSE      Enable/disable SWRF Metric Frequent Mode Collectio
t_mode                          n

_swrf_on_disk_enable TRUE       Parameter to enable/disable SWRF
d

_swrf_mmon_dbfus     TRUE       Enable/disable SWRF MMON DB Feature Usage
_swrf_test_dbfus     FALSE      Enable/disable DB Feature Usage Testing

_swrf_test_action 隐藏参数用以调试MMON的行为，设置该参数并10046事件:

SQL> alter session set "_swrf_test_action" = 28; 

Session altered.

SQL> alter session set "_swrf_test_action" = 10; 

Session altered.

[oracle@vrh4 ContentsXML]$ ps -ef|grep mmon
oracle    2872     1  0 18:28 ?        00:00:00 ora_mmon_SBDB
oracle    3446  3289  0 18:44 pts/1    00:00:00 tail -f SBDB_mmon_2872.trc
oracle    3997  3407  0 19:17 pts/2    00:00:00 grep mmon

SQL> oradebug setospid 2872;
Oracle pid: 15, Unix process pid: 2872, image: oracle@vrh4.oracle.com (MMON)

SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.

完成以上操作后等待一段时间，MMON进程的trace文件会陆续写出一些信息，如:

*** 2011-10-24 18:45:24.795
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.

*** 2011-10-24 18:46:24.874
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.

*** 2011-10-24 18:47:24.952
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.

*** 2011-10-24 18:48:25.053
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.

说明MMON每分钟都会自动刷新一定的数据到磁盘上。

此外还可以看到MMON清理过期快照的信息:

*** 2011-10-24 18:58:25.290
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ... KEWRAPM: Finished one MMON Auto-Purge cycle. KEWRAPC: Auto Purge Action Completed.

*** 2011-10-24 19:28:26.091
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ..
  KEWRAPM: Finished one MMON Auto-Purge cycle

*** 2011-10-24 19:58:27.041
KEWRAFM: Beginning one MMON Auto-Flush cycle ...
  Finished one MMON Auto-Flush cycle.
KEWRAPM: Beginning one MMON Auto-Purge cycle ...
  KEWRAPM: Finished one MMON Auto-Purge cycle.

可以看到在默认情况下MMON每30分钟会自动去清理一次Automatic Workload Repository自动负载仓库中的过期快照信息，当18:58:25.290的第一次清理工作完成后查询dba_hist_snapshot可以发现过期快照消失了:

SQL> select snap_id,
  2         to_char(begin_interval_time, 'YYYY-MM-DD'),
  3         to_char(end_interval_time, 'YYYY-MM-DD')
  4    from dba_hist_snapshot
  5   order by snap_id;

   SNAP_ID TO_CHAR(BE TO_CHAR(EN
---------- ---------- ----------
       194 2011-10-16 2011-10-16
       195 2011-10-16 2011-10-16
       196 2011-10-16 2011-10-16
       197 2011-10-16 2011-10-16
.................
       222 2011-10-24 2011-10-24

通过以上演示我们可知AWR快照的保留天数与SYSAUX的使用率并无关系，实际控制AWR保留天数的最主要因素是MMON何时、如何地清理过期快照？ MMON的清理操作直接受到dba_hist_wr_control.retention设置值的影响，默认情况10g 为保留7天，而11g为保留8天，MMON只已清理过期的快照。

同时KEWRAPM的trace信息也说明了,默认情况下MMON每30分钟做一次”MMON Auto-Purge cycle”清理工作。

二、清理SYAUX下的无效ASH信息

1.检查是否有无效的ASH信息
select count(*)
from sys.wrh$_active_session_history a
where not exists (select 1
from sys.wrm$_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number);
2.清理无效的ASH信息
delete
from sys.wrh$_active_session_history a
where not exists (select 1
from sys.wrm$_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number);
3.对ASH表清理后的碎片整理
alter table sys.wrh$_active_session_history enable row movement;
alter table sys.wrh$_active_session_history shrink space cascade;
alter table sys.wrh$_active_session_history disable row movement;
4.收集碎片整理后表的统计信息
EXEC dbms_stats.gather_table_stats(ownname => ‘SYS’,tabname => ‘WRH$_ACTIVE_SESSION_HISTORY’,cascade => TRUE);
3.检查表空间可收缩的的位置
select a.FILE#,
a.NAME,
a.BYTES / 1024 / 1024 mb,
ceil(HWM * A.BLOCK_SIZE) / 1024 / 1024 RESIZETO,
‘ALTER DATABASE DATAFILE ”’ || A.NAME || ”’ RESIZE ‘ ||
(trunc(CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024)+20) || ‘M;’ RESIZECMD
from v$datafile a,
(SELECT C.file_id, MAX(C.block_id + C.blocks – 1) HWM
FROM DBA_EXTENTS C
GROUP BY FILE_ID) B
WHERE A.FILE# = B.FILE_ID
AND a.tablespace=’SYSAUX’
ORDER BY 5;

三.SYSAUX清理后的检查

1.清理后的无效INDEX检查
select * from dba_indexes where status<>‘VALID’ AND STATUS<>‘N/A’;
SELECT * FROM DBA_IND_PARTITIONS WHERE STATUS<>‘USABLE’ AND STATUS<>‘N/A’;
SELECT * FROM DBA_IND_SUBPARTITIONS WHERE STATUS<>‘USABLE’;
上面语句应均无数据返回，如有则对这些INDEX进行重建
2.清理后的INDEX并行度检查
select * from dba_indexes where degree not in (’1′,’0′,’DEFAULT’);

oracle数据库清理和回收system和sysaux表空间

作者：dbdream

前几天和一个网友讨论了下SYSAUX表空间使用率过高的问题，今天有时间整理一下，正好我们的测试数据库也存在这个问题。本案例数据库版本为11.2.0.4.0。
SYSAUX表空间被称为系统辅助表空间，是10g版本开始推出的新功能，主要的目的是为SYSTEM表空间减负，Oracle对SYSTEM表空间的维护有一套独立的体系，对SYSTEM表空间操作会占用额外的CPU资源，而且效率低下，详见我之前发表的文章为什么不要把用户表存储到SYSTEM表空间。在10g版本，增加了SYSAUX辅助表空间，将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中，这样大大减少了SYSTEM表空间的消耗，也减少了Oracle对SYSTEM表空间维护的成本。
但是有几个比较坑爹的组件需要的表并没有挪到SYSAUX表空间，比如常见的审计用到的AUD$表，很多DBA都可能遇到SYSTEM表空间使用率过高，查询发现是AUD$表很大导致的，我们的数据库正好打开了审计功能，正好可以拿来做实验。
下面先查询下SYSTEM和SYSAUX表空间的使用率。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
----------------- -------------- ---------- --------------- --------------- ---------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 8251.7M 94.99% 434.3M
可见，SYSAUX表空间已经使用了21GB左右，SYSTEM表空间已经使用了8GB左右，下面查看下使用SYSTEM和SYSAUX表空间的比较大的表有哪些。

sys@IVLDB> select * from (
2 select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in
('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc)
3 where rownum <=20;

SEGMENT_NAME TOTAL_MB TABLESPACE_NAME
------------------------------------------------- ---------- --------------
AUD$ 6680 SYSTEM
WRH$_ACTIVE_SESSION_HISTORY 5248.0625 SYSAUX
WRH$_EVENT_HISTOGRAM_PK 2499.0625 SYSAUX
WRH$_EVENT_HISTOGRAM 1794.0625 SYSAUX
WRH$_LATCH_MISSES_SUMMARY_PK 905.0625 SYSAUX
WRH$_SQLSTAT 816.0625 SYSAUX
WRH$_LATCH 800.0625 SYSAUX
C_OBJ#_INTCOL# 768 SYSTEM
WRH$_LATCH_MISSES_SUMMARY 760.0625 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY_PK 712.0625 SYSAUX
WRH$_SYSSTAT_PK 672.0625 SYSAUX
WRH$_LATCH_PK 560.0625 SYSAUX
WRH$_SYSSTAT 504.0625 SYSAUX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 456 SYSAUX
WRH$_PARAMETER_PK 454.0625 SYSAUX
WRH$_SEG_STAT 408.0625 SYSAUX
WRH$_PARAMETER 384.0625 SYSAUX
WRH$_SYSTEM_EVENT 368.0625 SYSAUX
WRI$_OPTSTAT_HISTGRM_HISTORY 312 SYSAUX
I_H_OBJ#_COL# 312 SYSTEM

可见，大表大部分都是AUD$和WRH$开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间，下面再查看下SYSAUX表空间的使用情况，可以通过v$sysaux_occupants视图查询到。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 ORDER BY 1 ;

Item Space Used (GB) Schema Move Procedure
------------------------------ --------------- -------------------- -----------------------------------
AO .038391113 SYS DBMS_AW.MOVE_AWMETA
AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK .000366211 SYS
EM 0 SYSMAN emd_maintenance.move_em_tblspc
EM_MONITORING_USER .001708984 DBSNMP
EXPRESSION_FILTER .003540039 EXFSYS
JOB_SCHEDULER .010498047 SYS
LOGMNR .013061523 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY .001342773 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM .000427246 ORDSYS ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA .013244629 ORDDATA ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc
ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc
PL/SCOPE .001525879 SYS
SDO .064758301 MDSYS MDSYS.MOVE_SDO
SM/ADVISOR .199707031 SYS
SM/AWR 18.8637695 SYS
SM/OPTSTAT 1.14306641 SYS
SM/OTHER .012268066 SYS
SMON_SCN_TIME .008178711 SYS
SQL_MANAGEMENT_BASE .001647949 SYS
STATSPACK 0 PERFSTAT
STREAMS .000976563 SYS
TEXT .003540039 CTXSYS DRI_MOVE_CTXSYS
TSM 0 TSMSYS
ULTRASEARCH 0 WKSYS MOVE_WK
ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK
WM .003417969 WMSYS DBMS_WM.move_proc
XDB .123962402 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSAMD .005004883 OLAPSYS DBMS_AMD.Move_OLAP_Catalog
XSOQHIST .038391113 SYS DBMS_XSOQ.OlapiMoveProc

31 rows selected.

可见SM/AWR组件就使用了将近19GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间，而这些数据是可以定期清理的，都没有必要保留太长的时间。
下面先清理审计的数据，如果要保留部分AUD$里面记录的审计数据，可以把想要的数据插入到一张临时表，然后直接truncate这张表就可以了，truncate操作会直接回收AUD$占用的空间。

sys@IVLDB> truncate table AUD$;
Table truncated.
sys@IVLDB> select bytes/1024/1024 from dba_segments where segment_name='AUD$';
BYTES/1024/1024
---------------
5
可见，truncate这张表之后，6680M的空间直接降为5M，释放了大量的SYSTEM表空间的空间。下面再来回收下SYSAUX表空间，这个相对比较麻烦，也比较耗时。
如上文所示，通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，也有很多是视图，dba_hist_wr_control就是视图)。
sys@IVLDB> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1357933872 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

大家可能会有疑问了，AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢？这个问题我个人认为主要有以下两个原因，首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的，这个从下面的SQL就可以看出。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
16918966
sys@IVLDB> select min(snap_id),max(snap_id) from wrh$_active_session_history;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 15533
可以看到，ASH的数据从第一个快照开始一直都在保留，导致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包清理过期或者不需要的AWR数据，可以回收这部分空间。
sys@IVLDB> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 15500);
PL/SQL procedure successfully completed.
清理了AWR数据之后，你会发现SYSAUX表空间的空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 where occupant_name='SM/AWR';
Item Space Used (GB) Schema Move Procedure
-------------- --------------- -------------------- ------------------
SM/AWR 18.8638306 SYS
AWR的数据还是使用将近19GB的空间，查询SYSTEM和SYSAUX表空间的使用率会发现SYSTEM表空间的使用率已经降低了很多，因为上文TRUNCATE了AUD$表，这张表使用的是SYSTEM表空间，上文删除了很多AWR的数据，AWR的数据使用SYSAUX表空间，并没有回收。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');

TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- --------------- ---------- --------------- ------------- --------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 1990.25M 22.91% 6695.75M

查询WRH$_ACTIVE_SESSION_HISTORY表会发现数据已经被删除了很多，从删除之前的16918966条记录变为删除后的4706条记录，但是表的大小没变，还是5GB多。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
4706
下面通过MOVE操作回收这个表的水位线，来回收这部分被删除数据占用的空间。这个表是分区表，分区表不支持表级别的MOVE操作，直接对分区表进行MOVE操作会遇到ORA-14511错误。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move;
alter table WRH$_ACTIVE_SESSION_HISTORY move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
查看这个表的分区信息，只有两个分区。
sys@IVLDB> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
SEGMENT_NAME PARTITION_NAME GB
------------------------------ ------------------------------ ----------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1357933872_0 5.125
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN .000061035

下面按照分区进行MOVE操作，来回收空间。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1357933872_0;
Table altered.
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN;
Table altered.
对分区表进行MOVE之后，需要重建索引，查看这个表的索引信息。
sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY';
INDEX_NAME
------------------------------
WRH$_ACTIVE_SESSION_HISTORY_PK

这个表只有一个主键，而且是分区索引，也不能对分区索引直接进行REBUILD操作，否则会遇到ORA-14086错误。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild;
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

下面查看下这个索引的分区信息。
sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
PARTITION_NAME
------------------------------
WRH$_ACTIVE_1357933872_0
WRH$_ACTIVE_SES_MXDB_MXSN
然后按照分区进行重建索引。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0;
Index altered.
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN;
Index altered.
再次查看，WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
SUM(BYTES)/1024/1024
--------------------
2.0625
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
SUM(BYTES)/1024/1024
--------------------
.3125
可见，这个表大小由之前的5248.0625MB将为2.0625MB，重建索引后，索引的大小也由之前的712.0625MB降为了0.3125MB，这一张表就回收了6GB左右的空间。
sys@IVLDB> SELECT occupant_name "Item",
2 space_usage_kbytes / 1048576 "Space Used (GB)",
3 schema_name "Schema",
4 move_procedure "Move Procedure"
5 FROM v$sysaux_occupants
6 where occupant_name='SM/AWR';
Item Space Used (GB) Schema Move Procedure
-------------- --------------- -------------------- ------------------
SM/AWR 13.0436401 SYS

通过v$sysaux_occupants视图，可以查询到AWR占用空间由之前的将近19GB降为了不到13GB。按照同样的方法，回收下WRH$_EVENT_HISTOGRAM表的空间，这也是分区表表1794MB，只有一个索引(是主键)2499MB。
sys@IVLDB> select partition_name from dba_tab_partitions where table_name='WRH$_EVENT_HISTOGRAM';

PARTITION_NAME
------------------------------
WRH$_EVENT_HISTO_MXDB_MXSN
WRH$_EVENT__1357933872_0
sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN;
Table altered.
sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1357933872_0;
Table altered.
sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM';
INDEX_NAME
------------------------------
WRH$_EVENT_HISTOGRAM_PK
sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_EVENT_HISTOGRAM_PK';
PARTITION_NAME
------------------------------
WRH$_EVENT_HISTO_MXDB_MXSN
WRH$_EVENT__1357933872_0
sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN;
Index altered.
sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1357933872_0;
Index altered.

清理WRH$_ACTIVE_SESSION_HISTORY和WRH$_EVENT_HISTOGRAM两张表，SYSAUX表空间就释放出10GB的空间，其他占用SYSAUX空间比较大的表也可以安装这样的方法去释放空间，这里就不一一演示了。
可能有人会问了，既然已经删除了数据，那么如果不回收这部分空间，ORACLE就不会再使用这部分空间了吗？为什么非要回收呢？这个高水位线的问题主要影响以下几个方面，不只是使用SYSAUX表空间的表，对所有碎片较多的表都适用。
1.影响查询速度，因为这样的表本身比较大，索引也会很大，查询会很慢。
2.消耗资源，因为表和索引都很大，查询时会消耗很多I/O资源。
3.空间占用，虽然大部分数据被DELETE掉了，但是这部分空间仍旧是这个段(SEGMENT)的区(EXTENT)，即使可以再利用也只能是这个表的新增数据才可以使用，而且还得是所在表空间无法自动扩展或者没有足够的扩展空间的情况下，如果表空间可以自动扩展或者有足够的扩展空间，那么还是不会使用这部分空间的，这就导致表和索引会越来越大，占用的空间越来越大，而一旦将可扩展的空间用尽，那么其他表将无法扩展，DELETE掉的数据空间，其他对象是无法使用的。
除了AWR的保留策略会影响SYSAUX表空间的使用率外，AWR收集数据的级别也对SYSAUX表空间的使用率影响很大，AWR收集数据的级别由statistics_level参数控制，这个参数有三个值，BASIC、TYPICAL、ALL，BASIC表示关闭统计信息收集，TYPICAL表示普通收集级别，只收集够日常用的统计信息，ALL是最给力的，凡是ORACLE能收集的所有信息都要收集，所以生成数据量会很大，相对来说，对性能和占用空间的影响也是最大的，通常TYPICAL就已经够用了。
不同的版本statistics_level参数的默认值不同，有的版本默认值为ALL，有的版本默认值为TYPICAL，具体哪些版本使用ALL为默认值，我记不清了，如果您的数据库设置statistics_level参数的值为ALL，建议调整为TYPICAL。
sys@IVLDB> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ---------
statistics_level string TYPICAL
如果您的数据库也遇到了SYSAUX表空间很大的情况，建议在清理AWR数据时，回收这部分空间，对SYSAUX表空间的对象操作，基本不会影响数据库的正常使用，SYSAUX表空间存放的对象都是数据库运行非必须的对象，技术这个表空间损坏或者丢失，数据库一样可以正常运行。

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改时间：2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群小麦苗的微店

.............................................................................................................................................