本文主要是介绍辅助表空间的对象清理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
提炼出的执行语句
查询dbid以及snapid
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot where dbid =3611764846;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
10335 10415
删除快照 ,只留 最近10 条
SQL> begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10335,
high_snap_id => 10405,
dbid => 3611764846);
end;
/
PL/SQL 过程已成功完成。
验证
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot where dbid =3611764846;
收缩对象未使用空间 ，用sys帐号运行
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$_EVENT_HISTOGRAM enable row movement;
alter table WRH$_EVENT_HISTOGRAM shrink space cascade;
alter table WRH$_EVENT_HISTOGRAM 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;
alter table WRH$_SYSTEM_EVENT enable row movement;
alter table WRH$_SYSTEM_EVENT shrink space cascade;
alter table WRH$_SYSTEM_EVENT disable row movement;
删除20天前的统计数据
SQL> exec dbms_stats.purge_stats(sysdate-20);
原来WRI$_OPTSTAT_HISTGRM_HISTORY上有函数索引，导致shrink不可用。只好采用move的方式。
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
然后将失效索引rebuild。
truncate table WRI$_OPTSTAT_HISTGRM_HISTORY;
truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;
参考文档
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;
再次检查表空间使用率
供参考：Script:诊断SYSAUX表空间使用情况
http://www.askmaclean.com/archives/script-diag-sysaux-space-usage.html
sysaux 表空间不足问题处理
Oracle 作者：Mr_Man 时间：2016-10-26 09:41:20 4782 0
一现场，备份库alter 日志出现错误
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_SQLSTAT partition WRH$_SQLSTA_3344221469_3956 by 128 in tablespace SYSAUX
根据错误提示是sysaux 表空间满了
从10G 开始引入的,以前一些使用独立表空间或系统表空间的数据库组件现在在SYSAUX表空间中创建。通过分离这些组件和功能，SYSTEM表空间的负荷得以减轻.反复创建一些相关对象及组件引起SYSTEM表空间的碎片问题得以避免。
如果SYSAUX表空间不可用,数据库核心功能将保持有效;使用SYSAUX表空间的特点将会失败或功能受限。可以从V$SYSAUX_OCCUPANTS view 查看情况
V$SYSAUX_OCCUPANTS
V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.
Column Datatype Description
OCCUPANT_NAME VARCHAR2(64) Occupant name
OCCUPANT_DESC VARCHAR2(64) Occupant description
SCHEMA_NAME VARCHAR2(64) Schema name for the occupant
MOVE_PROCEDURE VARCHAR2(64) Name of the move procedure; null if not applicable
MOVE_PROCEDURE_DESC VARCHAR2(64) Description of the move procedure
SPACE_USAGE_KBYTES NUMBER Current space usage of the occupant (in KB)
查看那些occupant空间占用情况：
select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 from V$SYSAUX_OCCUPANTS;
SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
在Oracle10中表空间SYSAUX引入，oracle把统计信息存储在这里，这也是为了更好的优化system表空间，
我们可以用视图V$SYSAUX_OCCUPANTS 查看，oracle有哪些数据存贮在SYSAUX中。
oracle的SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER的统计信息都存储在SYSAUX中
查询当前SM/OPTSTAT的统计信息的保存时间
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
修改SM/OPTSTAT的统计信息的保存时间为10天
SQL> exec dbms_stats.alter_stats_history_retention(10);
PL/SQL procedure successfully completed
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
10
删除20天前的统计数据
SQL> exec dbms_stats.purge_stats(sysdate-20);
PL/SQL procedure successfully completed
查看当前有效的统计数据是到什么时间的
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
12-2月 -12 07.15.49.000000000 下午 +08:00
再删除7天前的统计数据
SQL> exec dbms_stats.purge_stats(sysdate-7);
PL/SQL procedure successfully completed
这个时候发现有效的统计信息时间已经变了
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
14-2月 -12 07.15.57.000000000 下午 +08:00
没有释放空间是因为“purge_stats”用delete的方式删除数据，虽然数据没了，但是HWM还没降下来，查看OPTSTAT使用哪些表，然后降低其高水位即可。
注意：占用空间的不一定都是object_name like '%OPTSTAT%'的对象，所以这个条件必要时可以去掉。
SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
OBJECT_NAME
--------------------------------------------------------------------------------
WRI$_OPTSTAT_TAB_HISTORY
WRI$_OPTSTAT_IND_HISTORY
WRI$_OPTSTAT_HISTHEAD_HISTORY
WRI$_OPTSTAT_HISTGRM_HISTORY
WRI$_OPTSTAT_AUX_HISTORY
WRI$_OPTSTAT_OPR
OPTSTAT_HIST_CONTROL$
7 rows selected
SQL>
再结合如下sql判断哪个表大，然后就move哪个表
SQL> select a.table_name,a.num_rows from dba_tables a where a.tablespace_name='SYSAUX' and a.table_name like '%OPTSTAT%';
TABLE_NAME NUM_ROWS
------------------------------ ----------
WRI$_OPTSTAT_OPR 151
WRI$_OPTSTAT_AUX_HISTORY 0
WRI$_OPTSTAT_HISTGRM_HISTORY 139933
WRI$_OPTSTAT_HISTHEAD_HISTORY 14406
WRI$_OPTSTAT_IND_HISTORY 1196
WRI$_OPTSTAT_TAB_HISTORY 1323
6 rows selected
SQL>
再用如下语句查出相关表的索引，因为move表，索引会失效，需要重建索引
SQL> select i.index_name,i.table_name,i.status,i.table_owner
from dba_indexes i,dba_objects s where i.table_name=s.object_name and s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
INDEX_NAME TABLE_NAME STATUS TABLE_OWNER
------------------------------ ------------------------------ -------- ------------------------------
I_WRI$_OPTSTAT_TAB_OBJ#_ST WRI$_OPTSTAT_TAB_HISTORY VALID SYS
I_WRI$_OPTSTAT_TAB_ST WRI$_OPTSTAT_TAB_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_OBJ#_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_IND_ST WRI$_OPTSTAT_IND_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_HH_ST WRI$_OPTSTAT_HISTHEAD_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_H_ST WRI$_OPTSTAT_HISTGRM_HISTORY VALID SYS
I_WRI$_OPTSTAT_AUX_ST WRI$_OPTSTAT_AUX_HISTORY VALID SYS
I_WRI$_OPTSTAT_OPR_STIME WRI$_OPTSTAT_OPR VALID SYS
10 rows selected
再次查看表空间的使用率，sysaux表空间确实减低不少。
alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
重建索引
这篇关于辅助表空间的对象清理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
