SYSAUX表空间在默认条件下你如果不做任何配置,随着时间的推移,会膨胀的越来越大!SYSAUX表空间做为SYSTEM表空间的辅助表空间,主要存放EM相关的内容以及表统计信息,AWR快照,审计信息等,个人认为,如果你的SYSAUX表空间增大到需要重新添加数据文件,那么该考虑让他减肥了!
如果sysaux表空间空间满了,并不会影响主要业务,但是如果数据库频繁收集统计信息,那就会造成意外的错误,甚至是数据库不可用,这是因为收集统计信息卡住,统计信息的重要性,就不用说了吧。
1、查看SYSAUX表空间内各个分类项目占存储空间的比重,可以看到AWR快照,统计信息。
SELECT occupant_name "Item",
space_usage_kbytes/1024/1024/1024 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc;
2、修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除
--最早的统计信息
SQL> selectdbms_stats.get_stats_history_availability from dual;
--保留时间
SQL> select dbms_stats.get_stats_history_retentionfrom dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> execdbms_stats.alter_stats_history_retention(15);
PL/SQL procedure successfullycompleted
SQL> select dbms_stats.get_stats_history_retentionfrom dual;
GET_STATS_HISTORY_RETENTION
---------------------------
15
删除统计信息(不建议操作)
exec dbms_stats.purge_stats(sysdate - 20);
当SGA是自动管理时,purge时会被MMON进程阻塞的。
3、修改AWR快照的保存时间为7天(7*24*60),每小时收集一次,也可以通过EM界面查看和修改
--检查当前系统的保留时间为8天,1小时采样一次
SQL> select * from dba_hist_wr_control;
DBIDSNAP_INTERVAL RETENTION TOPNSQL
------------------------------------------------- -------------------------------------------------
1494575446+0000001:00:00.0 +0000800:00:00.0 DEFAULT
SQL>
begin
dbms_workload_repository.modify_snapshot_settings(
interval => 60,
retention => 10080,--分钟
topnsql => 100
);
end;
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY",line 174
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY",line 222
ORA-06512: 在line 2
这里 691200(8*24*60*60),604800(7*24*60*60)都是以秒为单位的。
发现执行报错,因为当前系统移动窗口大于现在所设的时间窗口。
--查看系统的当前的MOVING_WINDOW_SIZE,
SQL> select dbid,baseline_name,baseline_type,moving_window_sizefrom dba_hist_baseline;
DBIDBASELINE_NAME BASELINE_TYPEMOVING_WINDOW_SIZE
---------------------------------------------------------------- ------------------
1494575446SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
--修改其大小为7,即7天。
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL procedure successfullycompleted
--再次执行修改AWR快照的保存时间
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>60,retention=>7*24*60 );
PL/SQL procedure successfullycompleted
注:当然也可通过EM进行修改AWR设置
4、删除AWR快照,再次查看SYSAUX表空间使用率。
--查询最最小和最大快照ID
SQL> select min(snap_id),max(snap_id) fromdba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
8188 8397
--删除最早的24个AWR快照,也就是最早的24小时的快照。
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>8188,high_snap_id => 8188+24);
最后,再次查看表空间发现使用率已经减小。
如果sysaux表空间空间满了,并不会影响主要业务,但是如果数据库频繁收集统计信息,那就会造成意外的错误,甚至是数据库不可用,这是因为收集统计信息卡住,统计信息的重要性,就不用说了吧。
1、查看SYSAUX表空间内各个分类项目占存储空间的比重,可以看到AWR快照,统计信息。
SELECT occupant_name "Item",
space_usage_kbytes/1024/1024/1024 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc;
2、修改统计信息的保持时间,默认为31天,这里修改为7天,过期的统计信息会自动被删除
--最早的统计信息
SQL> selectdbms_stats.get_stats_history_availability from dual;
--保留时间
SQL> select dbms_stats.get_stats_history_retentionfrom dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> execdbms_stats.alter_stats_history_retention(15);
PL/SQL procedure successfullycompleted
SQL> select dbms_stats.get_stats_history_retentionfrom dual;
GET_STATS_HISTORY_RETENTION
---------------------------
15
删除统计信息(不建议操作)
exec dbms_stats.purge_stats(sysdate - 20);
当SGA是自动管理时,purge时会被MMON进程阻塞的。
3、修改AWR快照的保存时间为7天(7*24*60),每小时收集一次,也可以通过EM界面查看和修改
--检查当前系统的保留时间为8天,1小时采样一次
SQL> select * from dba_hist_wr_control;
DBIDSNAP_INTERVAL RETENTION TOPNSQL
------------------------------------------------- -------------------------------------------------
1494575446+0000001:00:00.0 +0000800:00:00.0 DEFAULT
SQL>
begin
dbms_workload_repository.modify_snapshot_settings(
interval => 60,
retention => 10080,--分钟
topnsql => 100
);
end;
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY",line 174
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY",line 222
ORA-06512: 在line 2
这里 691200(8*24*60*60),604800(7*24*60*60)都是以秒为单位的。
发现执行报错,因为当前系统移动窗口大于现在所设的时间窗口。
--查看系统的当前的MOVING_WINDOW_SIZE,
SQL> select dbid,baseline_name,baseline_type,moving_window_sizefrom dba_hist_baseline;
DBIDBASELINE_NAME BASELINE_TYPEMOVING_WINDOW_SIZE
---------------------------------------------------------------- ------------------
1494575446SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
--修改其大小为7,即7天。
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL procedure successfullycompleted
--再次执行修改AWR快照的保存时间
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>60,retention=>7*24*60 );
PL/SQL procedure successfullycompleted
注:当然也可通过EM进行修改AWR设置
4、删除AWR快照,再次查看SYSAUX表空间使用率。
--查询最最小和最大快照ID
SQL> select min(snap_id),max(snap_id) fromdba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
8188 8397
--删除最早的24个AWR快照,也就是最早的24小时的快照。
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>8188,high_snap_id => 8188+24);
最后,再次查看表空间发现使用率已经减小。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31324783/viewspace-2132143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31324783/viewspace-2132143/