查看对象占用比：

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;

最后发现AWR占用最多，清理AWR相关数据，降低空间使用率。

修改统计信息的保持时间

 

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 过程已成功完成。

 

SQL> select dbms_stats.get_stats_history_retention from dual;

 

GET_STATS_HISTORY_RETENTION

---------------------------

                          7

 

 

修改快照收集间隔为1小时

SQL> begin

  2           dbms_workload_repository.modify_snapshot_settings (

  3              interval => 60,

  4              retention => 10080,

  5              topnsql => 100

  6            );

  7  end;

  8  /

 

PL/SQL 过程已成功完成。

 

验证：

 

SQL> select snap_interval,retention from dba_hist_wr_control;

 

SNAP_INTERVAL                                                               RETENTION

--------------------------------------------------------------------------- --------------------------------------------------------

+00000 01:00:00.0                                                           +00007 00:00:00.0

 

 

查询dbid以及snapid

 

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

 

      DBID BASELINE_NAME                                                    BASELINE_TYPE MOVING_WINDOW_SIZE

---------- ---------------------------------------------------------------- ------------- ------------------

3611764846 SYSTEM_MOVING_WINDOW                                             MOVING_WINDOW                  2

 

 

 

SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot where dbid =3611764846;

 

MIN(SNAP_ID) MAX(SNAP_ID)

------------ ------------

       10335        10415

 

删除快照

 

 

SQL> begin

  2       dbms_workload_repository.drop_snapshot_range(

  3         low_snap_id => 10335,

  4        high_snap_id => 10415,

  5        dbid => 3611764846);

  6  end;

  7  /

 

PL/SQL 过程已成功完成。

 

验证

 

SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot where dbid =3611764846;

 

MIN(SNAP_ID) MAX(SNAP_ID)

------------ ------------

       10416        10416


对象大小排序，求出前20.

select * from (

select owner,segment_name,segment_type,partition_name ,bytes/(1024*1024) size_m

from dba_segments

where tablespace_name = 'SYSAUX'

ORDER BY BLOCKS desc) where rownum<=20;

alter table WRH$_SEG_STAT  enable row movement;

alter table WRH$_SEG_STAT  shrink space cascade;

alter table WRH$_SEG_STAT  disable row movement;

alter table WRH$_SYSTEM_EVENT  enable row movement;

alter table WRH$_SYSTEM_EVENT  shrink space cascade;

alter table WRH$_SYSTEM_EVENT  disable row movement;

alter table WRH$_LATCH  enable row movement;

alter table WRH$_LATCH  shrink space cascade;

alter table WRH$_LATCH  disable row movement;

alter table WRH$_EVENT_HISTOGRAM  enable row movement;

alter table WRH$_EVENT_HISTOGRAM  shrink space cascade;

alter table WRH$_EVENT_HISTOGRAM  disable row movement;

alter table WRH$_SERVICE_STAT  enable row movement;

alter table WRH$_SERVICE_STAT shrink space cascade;

alter table WRH$_SERVICE_STAT  disable row movement;

alter table WRH$_ACTIVE_SESSION_HISTORY  enable row movement;

alter table WRH$_ACTIVE_SESSION_HISTORY  shrink space cascade;

alter table WRH$_ACTIVE_SESSION_HISTORY  disable row movement;

alter table WRH$_SYSSTAT  enable row movement;

alter table WRH$_SYSSTAT  shrink space cascade;

alter table WRH$_SYSSTAT  disable row movement;

alter table WRH$_LATCH_MISSES_SUMMARY  enable row movement;

alter table WRH$_LATCH_MISSES_SUMMARY  shrink space cascade;

alter table WRH$_LATCH_MISSES_SUMMARY  disable row movement;

alter table WRH$_PARAMETER  enable row movement;

alter table WRH$_PARAMETER  shrink space cascade;

alter table WRH$_PARAMETER  disable row movement;

alter table WRH$_SQLSTAT  enable row movement;

alter table WRH$_SQLSTAT  shrink space cascade;

alter table WRH$_SQLSTAT  disable row movement; 

BEGIN

    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (

                    window_size => 2, 

                  dbid => 7657298);

 END;