oracle sysaux爆满 alert trace 日志爆满

0、Oracle 数据库 alert日志及trace日志的清理
登录到数据库
sqlplus / as sysdba
修改参数：
SQL> alter system set trace_enabled=false;

System altered.

1、查询SYSAUX表空间内各个分类项目占存储空间的比重
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 

2、修改统计信息收集时间为7天
select dbms_stats.get_stats_history_retention from dual; 

  begin
        dbms_stats.alter_stats_history_retention(7);   
   end;   

3、修改AWR快照的保存时间为7天(7*24*60),每小时收集一次
begin 
         dbms_workload_repository.modify_snapshot_settings ( 
            interval => 60, 
            retention => 10080, 
            topnsql => 100 
          ); 
end; 

//如果报错 ORA-13541: system moving window baseline size (691200) greater than retention (604800)

select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

exec dbms_workload_repository.modify_baseline_window_size(2);
修改为2天

再次执行 修改快照保存时间
4、删除AWR快照
select dbid from v$database; //查询数据库ID

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; 
5、删除统计信息
 select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 exec dbms_stats.purge_stats(sysdate-16);
6、表收缩
alter table WRH$_SQLSTAT shrink space;
alter table WRH$_SYSSTAT shrink space;
alter table WRH$_SEG_STAT shrink space;
alter table WRH$_LATCH shrink space;

7、12C独有的审计日志
select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX'  order by 4 desc)  where  rownum<=10;

//删除审计日志
begin
    dbms_audit_mgmt.clean_audit_trail(
        audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        use_last_arch_timestamp  =>  FALSE);
end;
/

8、关闭listener.log监听日志

$ lsnrctl
LSNRCTL> set log_status off
LSNRCTL> save_config
LSNRCTL> show log_status