oracle 清理索引空间,Oracle 清理SYSAUX表空间

Oracle 清理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;

```

```

清理SYSAUX表空间：

1. 创建临时表，保存近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.

2. 禁用AWR

exec dbms_workload_repository.modify_snapshot_settings(interval => 0);

3. truncate WRH$_ACTIVE_SESSION_HISTORY

TRUNCATE TABLE sys.WRH$_ACTIVE_SESSION_HISTORY;

4. truncate 完成后，重建 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;

7. 启动AWR

exec dbms_workload_repository.modify_snapshot_settings(interval => 60);

8. 测试awr和ash可能正常生成

---手动生成snapshot

exec dbms_workload_repository.create_snapshot;

@?/rdbms/admin/awrrpt

@?/rdbms/admin/ashrpt

9. 删除备份表

drop table mingshuo.ash_bak_20190610 purge;

```

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