Oracle uses materialized views to synchronize table data

1. Create the original table and materialized view log

SQL > create table T1 (id int,name varchar2 (30)), Table created.SQL > alter table T1 add constraint pk_t1 primary key (id) using index;Table altered.SQL > create materialized view log on T1 with primary key;Materialized view log created.2. Create a target table and materialized view here I create a materialized view of refresh fast on commit type SQL > create table T2 as select * from T1 where 1 objective 2 position Table created.SQL > create materialized view T2 on prebuilt table refresh fast on commit as select * from T1 alternate material view created.3. A simple test inserts a piece of data into T1, and there exists data SQL > insert into T1 values (1 row created.SQL > commit;Commit complete.SQL > select * from T2) as soon as T2 is submitted. ID NAME- 1 A4.ddl test through the test, we found that the materialized view does not support ddl statements. We add a column and a rename column to T1 SQL > alter table T1 add ddl_test int;Table altered.SQL > alter table T1 rename column name to names;Table altered.SQL > select * from T2. ID NAME- 1 A 2 4SQL > insert into T1 values; 1 row created.SQL > commit;Commit complete.SQL > select * from T1 ID NAMES DDL_TEST- 1 A 2 4 3 x1234SQL > select * from T2 ID NAME- 1 A 2 4

When we find that the data has not come, let's take a look at the definition and state of the materialized view.

SQL > select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','T2') from dual DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW','T2')-CREATE MATERIALIZED VIEW "SCOTT". "T2" ("ID", "NAME") ON PREBUILT TABLE WITHSQL > select staleness from user_mviews STALENESS-COMPILATION_ERROR