0

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?

Michael Jin
  • 1
  • 1
  • Sounds like you might need to raise a service request to Oracle support. (Just curious - if you're recreating the mview every time the data changes anyway, is there any benefit in using an mview over a normal table?) – Alex Poole Jun 23 '22 at 11:52
  • @AlexPoole - thanks for the reply. Existing application has been using SQL with joins on those tables loaded by external party. I need to make sure the existing SQL still works and no changes at application side. The Mview is created to rewrite the SQL to avoid joins, after it is completely refreshed. – Michael Jin Jun 25 '22 at 10:25

0 Answers0