View Materializada atualiza automaticamente?

[fsitjaParticipante]

Você precisa criar um materialized view log e criar a materialized view com opção fast refresh on commit.
Se o SQL da view tiver joins você precisa criar um view log para todas as tabelas e incluir as opções de criar view log por rowid e incluindo as colunas das chaves estrangeiras.

http://download.oracle.com/docs/cd/E118 … s_6002.htm
http://download.oracle.com/docs/cd/E118 … s_6003.htm

Segue um exemplo para você verificar.


SQL> create table t1 (col1 number primary key);

Table created.

SQL> create materialized view log on t1;

Materialized view log created.

SQL> create materialized view mv_t1
2 build immediate
3 refresh fast on commit
4 as select col1 from t1;

Materialized view created.

SQL> select owner, table_name from all_tables where table_name = 'MLOG$_T1';

OWNER TABLE_NAME

---

CMZA MLOG$_T1

SQL> insert into t1 values (1);

1 row created.

SQL> select * from mlog$_t1;

  COL1 SNAPTIME D O CHANGE_VECTOR$$

---

     1 01/01/00 I N FE

SQL> select * from mv_t1;

no rows selected

SQL> commit;

Commit complete.

SQL> select * from mv_t1;

COL1

     1

SQL> select * from mlog$_t1;

no rows selected

SQL>