Résoudre l’ORA-600 doc id 2523249.1

Optimizer expression statistics monitoring is introduced in 12.2 database version.

 For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

Monitoring feature is controlled by parameter « _column_tracking_level ». High growth have been noticed when monitoring expression statistics is active, enabled by default.

 

 

Solution:

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;


Commentaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Translate »