Starting with the Oracle Database version 12.2, during the materialized view refresh, the below temporary tables are being created:
SYS.WRI$_ADV_OBJSPACE_TREND_DATA SYS.WRI$_ADV_SEGADV_SEGROW SYS.WRI$_ADV_OBJSPACE_CHROW_DATA
This mechanism is implemented to address a bug: 20387620. However, it is creating a contention on the database and the sessions cannot work properly. We choose to disable it:
SQL> alter system set "_mv_refresh_shrink_log" = FALSE;
This will prevent the segment advisor to kick in during the MV refresh…