oracle的sysaux使用量排查sql
水1篇工具sql
SELECT OCCUPANT_NAME,
OCCUPANT_DESC,
SCHEMA_NAME,
MOVE_PROCEDURE,
MOVE_PROCEDURE_DESC,
SPACE_USAGE_KBYTES SPACE_USAGE_KB,
ROUND(SPACE_USAGE_KBYTES / 1024 / 1024,2) SPACE_USAGE_G
FROM V$SYSAUX_OCCUPANTS D
ORDER BY D.SPACE_USAGE_KBYTES DESC;分享些经验：
1、一般的system爆满是audit_trail=db的audit日志。truncate table sys.aud可以快速清理。如果有使用audit日志。正常做法应该是用过程把其挪到其他表空间，然后配置删除策略
挪动用的proc
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_TBS');
END;
/
可以再配置以下过程自动清理
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_purge_interval => 100 /* hours */,
audit_trail_purge_name => 'CLEANUP',
use_last_arch_timestamp => TRUE);
END;
/2、sysaux的爆满一般则是addm也就是俗称的awr组件的问题。可以用以下过程来清理快照
execute dbms_workload_repository.drop_snapshot_range(low,high,dbid);