查看SYSAUX信息
set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
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
/
col OCCUPANT_NAME for a25
col OCCUPANT_DESC for a60
col SCHEMA_NAME for a20
col MOVE_PROCEDURE for a40
col SPACE_USAGE_KBYTES for a20
select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,MOVE_PROCEDURE,SPACE_USAGE_KBYTES from v$sysaux_occupants where occupant_name='LOGMNR';
保留多久的旧统计数据
1 |
|
将旧统计信息的保留时间设置为10天
1 |
|
清除超过10天的统计数据(如果有大量数据(sysdate-30,sydate-25等),最好分阶段执行此操作
1 |
|
显示尚未清除的可用统计信息
1 |
|
显示表的大小,并在清除统计信息后重建
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
显示清除统计信息后索引为重建做好准备的程度
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
请注意,由于索引是基于函数的,因此无法启用行移动和缩小表
1 2 3 4 5 |
|
|
运行rebuild table命令 - 请注意,这会导致任何gather_stats作业失败
alter table WRI $ _OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter table WRI $ _OPTSTAT_IND_HISTORY move tablespace sysaux;
alter table WRI $ _OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter table WRI $ _OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter table WRI $ _OPTSTAT_AUX_HISTORY move tablespace sysaux;
alter table WRI $ _OPTSTAT_OPR move tablespace sysaux;
alter table WRH $ _OPTIMIZER_ENV move tablespace sysaux;</span>
用于生成重建语句的脚本
select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
完成后,最好检查索引(索引)是否可用
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
最后,让我们看看保存日期为1天的空间以及SYSASDM模式的收集模式统计信息
exec dbms_stats.alter_stats_history_retention(1);
select dbms_stats.get_stats_history_retention from dual;