oracle olap创建物化视图,Oracle 物化视图同步表-roid

一、在目标库建立DBLINK

目标库操作：

主机B中建立TNS(tnsname.ora),也可以使用netca建立TNS

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

[oracle@rac1 admin]$ vi tnsnames.ora

ORCL=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.67.163)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

)

)

[oracle@rac1 admin]$ !sql

sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 20 09:17:10 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> grant create public database link,create database link to sguap206;

SQL> conn sguap206/sguap206

Connected.

SQL> create public database link sguap_linkconnect to sguap206identified by sguap206using 'ORCL';

link名称数据库A的用户数据库A的密码

Database link created.

SQL>  select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER                          OBJECT_NAM

------------------------------ ----------

PUBLIC                         SGUAP_LINK

SQL> SELECT SYSDATE FROM DUAL@SGUAP_LINK;

SYSDATE

---------

20-JAN-15

至此DBLIKE测试成功。

二、创建物化视图对表进行增量同步

1、在源库上建立物化视图日志

SQL> CREATE MATERIALIZED VIEW LOG ON PF_ALARM_TYPE WITH SEQUENCE, ROWID (TYPE_ID,TYPE_INFO)  INCLUDING NEW VALUES;表的列

Materialized view log created

使用rowid,sequence捕获数据变化情况。

注意：加上including new values子句，是为了记录数据修改前的值。

2、在目标库上创建定时增量更新的物化视图

SQL> create materialized view PF_ALARM_TYPE

2  refresh fast on demand

3  start with sysdate next sysdate+1/288 with rowid

4  as

5  select * from PF_ALARM_TYPE@SGUAP_LINK;

create materialized view PF_ALARM_TYPE

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

报错原因：说明你创建的物化视图名称和表名相同，可以修改物化视图的名称或者把那个表删除(谨慎)。

我这里使用删除表的操作(谨慎)。

SQL> create materialized view PF_ALARM_TYPE

2  refresh fast on demand

3  start with sysdate next sysdate+1/288 with rowid

4  as

5  select * from PF_ALARM_TYPE@SGUAP_LINK;

Materialized view created.

如果跨库跨平台时,有时必须要使用with primary key，物化视图对应的表上没有主键，如果需要索引可以另行添加。

SQL> select * from PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- --------------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

9 rows selected

发现数据第一次成功同步到目标库。

测试insert:在源库插入一条数据，查看目标库。

SQL> INSERT INTO PF_ALARM_TYPE VALUES(10 ,'TEST');

1 row inserted

SQL> COMMIT;

Commit complete

目标库查看：

发现还没有同步过来，等待5分钟

SQL> SELECT * FROM PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- ---------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

9 rows selected

SQL> SELECT * FROM PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- --------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

10 TEST

10 rows selected

发现刚刚在源库插入的数据已经同步到目标库上。

测试update:在源库更新一条数据，查看目标库。

SQL> update PF_ALARM_TYPE set type_info='OK' where type_info='TEST';

1 row updated

SQL> commit;

Commit complete

SQL> select * from PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- ---------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

10 OK

rows selected

目标库查看

SQL> SELECT * FROM PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- -------------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

10 OK

10 rows selected

测试delete:在源库删除一条数据，查看目标库。

SQL> DELETE PF_ALARM_TYPE WHERE TYPE_ID=10;

1 row deleted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- -------------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

9 rows selected

目标库查看同步情况：

SQL> SELECT * FROM PF_ALARM_TYPE;

TYPE_ID TYPE_INFO

--------------------------------- --------------

1无效卡

2门开超时

3强制开门

4反潜回

5胁迫开门

6互锁管制

7火警

8脱机

9控制器拆卸

9 rows selected

发现源库删除的数据目标库也删除了。