ORA-12000: a materialized view log already exists on table ‘XTBL’

Can we have multiple materialized view logs for a base object?

The answer is no. One materialized view log per base object can be used.

kIsH@STAX<>create materialized view log on xtbl  with sequence,rowid(XID,XDATE) including new values;
create materialized view log on xtbl  with sequence,rowid(XID,XDATE) including new values
*
ERROR at line 1:
ORA-12000: a materialized view log already exists on table 'XTBL'

Check the existing mview logs and drop if required

kIsH@STAX<>select count(*) from dba_mview_logs where master='XTBL';

  COUNT(*)
----------
         1

Leave a Reply