Hi,
We are moving from 11g (11.2.0.4) database to 19c and we are experiencing the following issue when refreshing our MVs. This also occurs when using dbms_mviw.refresh package to do the refresh. All refreshes are COMPLETE refreshes. The package refresh_data.process below refreshes the MVs in turn. On 19.0.0 version we had 2/3 MVs failing, on version 19.7.0.0.0 in one instance we had 10-15 MVs failing. As a work around we can drop and recreate the MV but lately with more MVs this is not feasible.
Has anyone seen this issue ?
Adrian
exec refresh_data.process;
BEGIN refresh_data.process; END;
*
ERROR at line 1:
ORA-20030: Complete refresh of materialized view cca_product__prd_xlate_h_mv
failed.
Was out-of-place refresh?:N
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3012
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2424
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2405
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2968
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3255
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3287
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "A1745373C1_REPORTING.REFRESH_MVIEW_CTL", line 25
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 97
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3012
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2424
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2405
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2968
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3255
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3287
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "A1745373C1_REPORTING.REFRESH_MVIEW_CTL", line 25
----- PL/SQL Call Stack -----
object line object
handle number name
0xd89c7f80 129 package body
A1745373C1_REPORTING.REFRESH_DATA.REFRESH_MVIEW
0xd89c7f80 1052 package body A1745373C1_REPORTING.REFRESH_DATA.PRODUCT
0xd89c7f80 3545 package body A1745373C1_REPORTING.REFRESH_DATA.PROCESS
0xf687eeb0 1 anonymous block
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 3723
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 1080
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 129
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 1052
ORA-06512: at "A1745373C1_REPORTING.REFRESH_DATA", line 3545
ORA-06512: at line 1