oracle11.2.0.4辅助表空间的对象清理:
查询对象占用空间的多少
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小时
exec
dbms_workload_repository.modify_baseline_window_size(2);
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;
收缩对象未使用空间
SQL> alter table
WRH$_ACTIVE_SESSION_HISTORY enable row
movement;
表已更改。
SQL> alter table
WRH$_ACTIVE_SESSION_HISTORY shrink space
cascade;
表已更改。
SQL> alter table
WRH$_ACTIVE_SESSION_HISTORY disable row
movement;
表已更改。
SQL> alter table WRH$_EVENT_HISTOGRAM enable row movement;
表已更改。
SQL> alter table WRH$_EVENT_HISTOGRAM shrink space cascade;
表已更改。
SQL> alter table WRH$_EVENT_HISTOGRAM disable row movement;
表已更改。
SQL> alter table WRH$_SYSSTAT enable
row movement;
表已更改。
SQL> alter table WRH$_SYSSTAT shrink
space cascade;
表已更改。
SQL> alter table WRH$_SYSSTAT disable
row movement;
表已更改。
SQL> alter table
WRH$_LATCH_MISSES_SUMMARY enable row
movement;
表已更改。
SQL> alter table
WRH$_LATCH_MISSES_SUMMARY shrink space
cascade;
表已更改。
SQL> alter table
WRH$_LATCH_MISSES_SUMMARY disable row
movement;
表已更改。
SQL> alter table WRH$_PARAMETER enable row movement;
表已更改。
SQL> alter table WRH$_PARAMETER shrink space cascade;
表已更改。
SQL> alter table WRH$_PARAMETER disable row movement;
表已更改。
SQL> alter table WRH$_SQLSTAT enable
row movement;
表已更改。
SQL> alter table WRH$_SQLSTAT shrink
space cascade;
表已更改。
SQL> alter table WRH$_SQLSTAT disable
row movement;
表已更改。
SQL> alter table WRH$_SEG_STAT enable
row movement;
表已更改。
SQL> alter table WRH$_SEG_STAT shrink
space cascade;
表已更改。
SQL> alter table WRH$_SEG_STAT disable row movement;
表已更改。
SQL> alter table WRH$_SYSTEM_EVENT enable row movement;
表已更改。
SQL> alter table WRH$_SYSTEM_EVENT shrink space cascade;
表已更改。
SQL> alter table WRH$_SYSTEM_EVENT disable row movement;
表已更改。
SQL> select
segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from
dba_segments where tablespace_name='SYSAUX' and
segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024
----------------------------------------
------------------------------ ------------------
---------------
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2271136883_0 TABLE PARTITION
3450.75
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION
.0625
Elapsed: 00:00:00.17
SQL> alter table sys.wrh$_active_session_history truncate
partition WRH$_ACTIVE_2271136883_0 update global indexes;
再次检查表空间使用率