1、检查MMON 进程
ps -ef|grep mmon
alter system enable restricted session;
alter system disable restricted session;
--同时执行
为了尽可能的减少设置restricted模式带来的影响,所以建议最好两个语句一起复制执行。
Tips:启用restricted模式后,应用新连接数据库的会话将直接报错ORA-12526无法连接,
直到禁用restricted模式后才可以恢复,
2、检查 sysaux空间
--表空间
select TABLESPACE_NAME,ROUND((t.TABLESPACE_SIZE * p.value)/1024/1024/1024)
TABLESPACE_SIZE_GB,ROUND((t.USED_SPACE * p.value)/1024/1024/1024) USED_SPACE_GB,USED_PERCENT
from DBA_TABLESPACE_USAGE_METRICS t
INNER JOIN v$parameter p ON p.name = 'db_block_size' order by 4 desc;
--表空间
SELECT a.snap_id,
c.tablespace_name ts_name,
to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime,
round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb
FROM dba_hist_tbspc_space_usage a,
(SELECT tablespace_id,
substr(rtime, 1, 10) rtime,
max(snap_id) snap_id
FROM dba_hist_tbspc_space_usage nb
group by tablespace_id, substr(rtime, 1, 10)) b,
dba_tablespaces c,
v$tablespace d
where a.snap_id = b.snap_id
and a.tablespace_id = b.tablespace_id
and a.tablespace_id=d.TS#
and d.NAME=c.tablespace_name
and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30
order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc;
2.1 清理sysaux表空间历史数据
1、创建新的分区
如果WRH$_表无法自动创建分区,可以用如下SQL创建新的分区,用于保留最新的数据:
alter session set "_swrf_test_action" = 72;
--手工创建一个快照
exec dbms_workload.create_snapshot()
**2、truncate旧的分区
**
生成truncate分区的SQL
select 'alter table '||object_name||' truncate partition '||subobject_name||' update global indexes;' from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE PARTITION' and created<sysdate-1 ;
检查输出的SQL,并执行。
3、truncate未分区的表
通过如下SQL找出大表:
select owner,table_name,dbms_xplan.format_number(num_rows) num_rows,object_type,partition_name,(select count(*) from dba_tab_partitions p where s.owner=p.table_owner and s.table_name=p.table_name) part from dba_tab_statistics s where owner in ('SYS','SYSTEM') and table_name like 'WRH$%' and num_rows>1e6 order by s.num_rows;
根据排序直接truncate相关表:
truncate table WRH$_TABLESPACE_SPACE_USAGE update global indexes;
truncate table WRH$_EVENT_HISTOGRAM update global indexes;
truncate table WRH$_MUTEX_SLEEP update global indexes;
truncate table WRH$_ENQUEUE_STAT update global indexes;
truncate table WRH$_SYSMETRIC_SUMMARY update global indexes;
truncate table WRH$_BG_EVENT_SUMMARY update global indexes;
truncate table WRH$_SYSMETRIC_HISTORY update global indexes;
truncate table WRH$_SQL_BIND_METADATA update global indexes;
truncate table WRH$_SQL_PLAN update global indexes;
4、定期清理
默认会自动清理,也可以使用如下SQL定期手工清理
select dbid,min(snap_id) from WRH$_SQL_PLAN group by dbid;
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(dbid,start_id,end_id);
5、减少保留的时长或细粒度
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8);
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>8*60*24);