EXP_HEAD$占用SYSAUX空间大的处理办法

栏目：eas cloud知识作者：金蝶来源：金蝶云社区发布：2024-09-22浏览：1

一、适用场景

适用于SYSAUX表空间爆满，且EXP_HEAD$组件占用高的情况，如果不是EXP_HEAD$组件占用高则不适用于本办法

查看下SYSAUX表空间下哪个组件占用高

SELECT * FROM (SELECT SEGMENT_NAME,

PARTITION_NAME,

SEGMENT_TYPE,

BYTES / 1024 / 1024

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'SYSAUX'

ORDER BY 4 DESC)

WHERE ROWNUM <= 10;

二、操作步骤

以下步骤按顺序执行

1. Disable future monitoring:

alter system set "_column_tracking_level"=17 scope=both;
--wait 10 minutes

2. To purge data from tables:

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO(); --执行时间比较久（30分钟左右）
truncate table sys.exp_head$ drop storage;
truncate table sys.exp_obj$ drop storage;
truncate table sys.exp_stat$ drop storage;
alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;
alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;
alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

3.To re-enable expression statistics monitoring later :

alter system set "_column_tracking_level"=21 scope=both; --非oracle19c 执行这条

Starting in 19c we would change this to 53 since that is the default value for this version higher.

alter system set "_column_tracking_level"=53 scope=both; --oracle 19c需要执行这条