清理SYSAUX表空间

作者: 这货不是王马勺 | 来源:发表于2021-11-01 10:37 被阅读0次

清理SYSAUX表空间
Oracle 数据库SYSAUX空间不足解决办法
北京纬创
SYSAUX 表空间减肥
mysql表碎片清理和表空间收缩
如何解决SYSAUX空间问题
数据存储遇到坑的总结
清理空间
清理空间
清理空间

SYSAUX表空间：为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等。
如果SYSAUX表空间空间满了，并不会影响主要业务，但是如果数据库频繁收集统计信息，那就会可能出问题，甚至由于收集统计信息hang住，导致数据不可用，对于SYSAUX表空间，也要定期进行检查，确保还有空余空间，如果空间满了，需要删除统计信息以及AWR快照等。

相关报错：

    ORA-1691: unable to extend lobsegment SYS.SYS_LOB0000000208C00005$$ by 128 in tablespace sysaux

查询SYSAUX表空间使用情况：

COL Item FOR A30
COL Schema FOR A30
SELECT occupant_name "Item",
  space_usage_kbytes / 1048576 "Space Used (GB)",
  schema_name "Schema",
  move_procedure "Move Procedure"
FROM v$sysaux_occupants
  ORDER BY 2 desc

SM/AWR:如果它的值大那么表示AWR信息容量大。
SM/OPTSTAT:如果它的值大那么表示优化器统计信息容量大。

查询SYSAUX表空间中具体哪个表占用率大：

select * from (
select segment_name,SEGMENT_TYPE,sum(bytes)/1024/1024 total_mb from dba_segments where tablespace_name =
'SYSAUX' group by segment_name,SEGMENT_TYPE order by 3 desc)
where rownum <=20;

查询快照信息：

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

清理ASH表：

创建临时表，保存近8天的数据

create table mingshuo.ash_bak_20190610 as select * from sys.WRH$_ACTIVE_SESSION_HISTORY where SAMPLE_TIME>=sysdate-9;
SQL> select count(*)  from mingshuo.ash_bak_20190610;
  COUNT(*)
----------
     12648
1 row selected.

禁用AWR

exec dbms_workload_repository.modify_snapshot_settings(interval => 0);

3.清理ASH表

TRUNCATE TABLE sys.WRH$_ACTIVE_SESSION_HISTORY;

4.重建 WRH$_ACTIVE_SESSION_HISTORY表的索引，并从备份表恢复数据

insert into sys.WRH$_ACTIVE_SESSION_HISTORY select * from mingshuo.ash_bak_20190610;
commit;

5.查看索引状态

set line 300 pages 200
col owner for a20
col index_name for a30
select index_owner,
index_name,
partition_name,
status,
tablespace_name,
last_analyzed
from dba_ind_partitions
where index_name in (select index_name
from dba_indexes
where table_name in ('WRH$_ACTIVE_SESSION_HISTORY')
and table_owner = 'SYS');

6.如果索引失效重建索引

alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK rebuild parallel 8 nologging;
alter index sys.WRH$_ACTIVE_SESSION_HISTORY_PK noparallel;

启动AWR

exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

测试awr和ash可能正常生成
手动生成snapshot

exec dbms_workload_repository.create_snapshot;
@?/rdbms/admin/awrrpt
@?/rdbms/admin/ashrpt

9.删除备份表

drop table mingshuo.ash_bak_20190610 purge;

清理表WRI $_ADV_OBJECTS表：如果表WRI$ _ADV_OBJECTS比较大，删除任务AUTO_STATS_ADVISOR_TASK会需要很多的undo表空间
可以通过以下方式purge数据，不会过度的产生redo/undo数据

### Check the no.of rows in WRI$_ADV_OBJECTS for Auto Stats Advisor Task ###
SQL> select count(*) from wri$_adv_objects where task_id=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');
 
  COUNT(*)
----------
  46324479
 
### Do CTAS from WRI$_ADV_OBJECTS to keep the rows apart from AUTO_STATS_ADVISOR_TASK ###
SQL> create table wri$_adv_objects_new as select * from wri$_adv_objects where task_id !=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');
SQL> select count(*) from wri$_adv_objects_new;
 
  COUNT(*)
----------
       359
 
### Truncate the table ###
SQL> truncate table wri$_adv_objects;
 
### Insert the rows from backed up table WRI$_ADV_OBJECTS_NEW to restore the records of ther advisor objects ###
SQL> insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new;
SQL> commit;
SQL> drop table wri$_adv_objects_new;
 
### Reorganize the indexes ###
SQL> alter index wri$_adv_objects_idx_01 rebuild;
SQL> alter index wri$_adv_objects_pk rebuild;

AO这张表的清理方法参考https://www.cnblogs.com/abclife/p/9371041.html
参考http://blog.itpub.net/69975956/viewspace-2703946/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69975956/viewspace-2703946/，如需转载，请注明出处，否则将追究法律责任。