Oracle uses materialized views for table data synchronization

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 viewHere I create a materialized view of the refresh fast on commit type

Sql> CREATE TABLE t2 as SELECT * from T1 where 1=2; Table created. sql> create materialized View T2 on prebuilt table refresh fast in commit as SELECT * from T1; Materialized view created.

3.简单测试 在t1插入一条数据，一提交t2即存在数据

sql> INSERT INTO T1 values (1, ' A '); 1 row created. Sql> commit; Commit complete. Sql> SELECT * from T2;id NAME----------------------------------------1 A

4.ddl测试 通过测试我们发现物化视图不支持ddl语句 我们给t1添加一个列和rename一个列

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 (3, ' X ', 1234); 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 

Discovery data is not coming, let's take a look at the definition and state of materialized views

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

Oracle uses materialized views for table data synchronization