表空间管理----SYSAUX表空间

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);
最后，再次查看表空间发现使用率已经减小。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31324783/viewspace-2132143/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31324783/viewspace-2132143/