Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aidan.

Asked: January 09, 2020 - 6:56 pm UTC

Last updated: January 13, 2020 - 3:09 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Can real time materialized views be implemented on remote database using dB links?

and Connor said...

Unfortunately not, because we don't know when the remote site issued a commit

SQL> create table t ( x int primary key, y varchar2(10));

Table created.

SQL> create materialized view log on t with primary key;

Materialized view log created.

SQL>
SQL> create materialized view MV1
  2  refresh fast on commit
  3  as select * from t;

Materialized view created.

SQL>
SQL> create database link looper using 'db18_pdb1';

Database link created.

SQL> select * from t@looper;

no rows selected

SQL>
SQL> create materialized view MV2
  2  refresh fast on commit
  3  as select * from t@looper;
create materialized view MV2
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 10, 2020 - 11:12 pm UTC

Do Real Time MViews only work when there is aggregation in the the Mview query?

Connor McDonald
January 13, 2020 - 3:09 am UTC

No - see my first example.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.