Recreate Oracle Materialized View failed after drop

684 views Asked by At

I have a Materialized View that is built by joining tables loaded by external party every day, with potential different structure (such as new columns). To adapt table structure changes, I built a daily batch that drops and recreates the Materialized View based on latest tables, followed by Complete Refresh.

The batch failed sometimes with error ORA-00001: unique constraint (SYS.I_OBJ1) violated after it called drop materialized view, and then attempted to recreate it. However, the re-creation will succeed if it is retried one second or so after the failure.

The SYS.I_OBJ1 is an unique index on SYS.OBJ$(OBJ#, OWNER#, TYPE#)

Below are SQL the batch runs to drop and recreate the Mview respectively:

drop materialized view t_mv

create materialized view t_mv
build deferred
using no index
enable query rewrite
as
    select /*+ full(a) full(b) parallel(a, 8) parallel(b, 8) */
        a.*
        b.col1,
        b.col2
    from tbl_a a, tbl_b b
    where a.id_col = b.id_col

I suspect the error occurred when Oracle is still finishing the removal of the existing MView, while the recreation is kicked off. But I can't find a way to prove it. I am with Oracle 19c database.

Can anyone please shed some lights here?

0

There are 0 answers