ORACLE -物化视图

Question

我正在尝试创建物化视图。

我有两个数据库ORA1和ORA2。我在ORA1上有表POKUS，我试图在ORA2上创建视图，当我在表POKUS中的ORA1上更改和压缩更改时，视图每次都会更改。

这是我的问题。但它不起作用：

CREATE MATERIALIZED VIEW  
REPLIKOVANY_POKUS2 
REFRESH FAST START WITH 
    SYSDATE NEXT SYSDATE + 1/24/60 
ENABLE QUERY REWRITE AS 
    SELECT * 
    FROM dovondrda3.POKUS@ora1.uhk.cz;

你能帮我个忙吗

Answer 1

每次我在ORA1上更改和压缩更改时，

都会更改

你不能这样做，不能通过数据库链接，因为当涉及到远程数据库时，刷新on commit将不起作用。它记录在数据仓库指南中的某个地方；如果需要，可以搜索它。

远程数据库：

SQL> create table a1_brisime as
  2  select * from tom
  3  where sifra = '0387779';

Table created.

SQL> alter table a1_brisime add constraint pkab primary key (sifra);

Table altered.

SQL> create materialized view log on a1_brisime;

Materialized view log created.

SQL>

本地数据库：

SQL> create materialized view mv_test
  2  refresh fast on commit as
  3  select sifra, naziv
  4  from a1_brisime@dbl_test
create materialized view mv_test
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


SQL>

就像我说的-它不会起作用的。因此，切换到另一个选项，例如on demand

SQL> create materialized view mv_test
  2  refresh on demand as
  3  select sifra, naziv
  4  from a1_brisime@dbl_test

Materialized view created.

SQL>

或计划的(如您所尝试的)：

SQL> create materialized view mv_test
  2  refresh complete
  3  start with sysdate
  4  next sysdate + 1/24/60
  5  as
  6  select sifra, naziv
  7  from a1_brisime@dbl_test

Materialized view created.

SQL>