SYSAUX表空间中的SYS.EXP_HEAD$表，占用大量空间

1、案例概述

客户有一套19C的CDB环境，其中一个PDB的SYSAUX表空间的空间使用竟然高达80GB左右。在以前的认知中，SYSAUX表空间最多只有十几个GB，是什么东西占用了这么大的空间呢，想弄个明白。

2、案例分析

2.1 运行awrinfo.sql脚本，先找出是哪些对象占用空间，信息如下所示。

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|                                                                                                                            
| Total SYSAUX size                     83,142.0 MB ( 74% of 112,636.0 MB MAX with AUTOEXTEND OFF )                          
|                                                                                                                            
| Schema  SYS          occupies         83,064.0 MB (  99.9% )                                                               
| Schema  XDB          occupies             53.9 MB (   0.1% )                                                               
| Schema  AUDSYS       occupies             15.8 MB (   0.0% )                                                               
| Schema  WMSYS        occupies              6.6 MB (   0.0% )                                                               
| Schema  GSMADMIN_INT occupies              1.4 MB (   0.0% )                                                               
| Schema  SYSTEM       occupies              0.3 MB (   0.0% )                                                               
| Schema  DBSNMP       occupies              0.2 MB (   0.0% )                                                               

......

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE                       
--------- --------- --------------------------------------------------------------------- ---------------                    
NON_AWR    32,660.0 SYS.EXP_HEAD$                                                         TABLE                              
NON_AWR    23,712.2 SYS.SYS_LOB0000011147C00004$$                                         LOBSEGMENT                         
NON_AWR    14,657.0 SYS.I_EXP_HEAD$                                                       INDEX                              
NON_AWR     1,632.0 SYS.WRI$_ADV_OBJECTS                                                  TABLE                              
NON_AWR     1,520.0 SYS.WRI$_SQLSET_PLANS                                                 TABLE                              
NON_AWR       936.2 SYS.SYS_LOB0000009222C00039$$                                         LOBSEGMENT                         
NON_AWR       762.0 SYS.WRI$_ADV_OBJECTS_IDX_02                                           INDEX                              
NON_AWR       750.0 SYS.WRI$_ADV_OBJECTS_IDX_01                                           INDEX                              
NON_AWR       584.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SYS_P181                            TABLE PARTITION                    
NON_AWR       504.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX                              
NON_AWR       426.0 SYS.WRI$_ADV_OBJECTS_PK                                               INDEX                              
NON_AWR       395.0 SYS.WRI$_SQLSET_PLAN_LINES                                            TABLE                              
NON_AWR       378.0 SYS.WRI$_SQLSET_STATISTICS                                            TABLE                              
NON_AWR       376.0 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX                              
NON_AWR       321.0 SYS.WRI$_ADV_SQLT_PLAN_HASH                                           TABLE                              
NON_AWR       248.0 SYS.WRI$_SQLSET_STATEMENTS                                            TABLE                              
NON_AWR       240.0 SYS.WRI$_ADV_SQLT_PLAN_HASH_01                                        INDEX                              
NON_AWR       190.0 SYS.WRI$_SQLSET_MASK_PK                                               INDEX                              
NON_AWR       176.0 SYS.WRI$_SQLTEXT_REFCOUNT_PK                                          INDEX                              
NON_AWR       169.0 SYS.WRI$_ADV_SQLT_PLAN_STATS                                          TABLE                              

可以看出，SYS.EXP_HEAD$这张表是元凶， 这张表是干什么用的呢？ 以前对它没什么了解。只能查查ORACLE官方文档了。

2.2 根据文章《Expression Tracking Feature Cannot Be Turned Off By OPTIMIZER_FEATURES_ENABLE(KB132474)》和《DMU reporting unconvertible data for EXP_HEAD$(KB132412)》的介绍，sys.exp_head$是12.2版本中引入的优化器新特性，用于收集SQL查询中的表达式使用统计信息。 SYS.EXP_HEAD$表中的数据如何处理？根据另外一篇internal的文章，可以关闭该特性，并且清空该对象中的数据，具体如下。

3、案例处理

Disable future monitoring:
alter system set "_column_tracking_level"=17 scope=both;
--wait 10 minutes

To purge data from tables:
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO() ;
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;

To re-enable expression statistics monitoring later :
alter system set "_column_tracking_level"=21 scope=both;

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;