建立远程物化视图

来源：互联网发布：网络传播概论新编 pdf 编辑：程序博客网时间：2024/05/16 23:32

一，配置监听

[oracle@dsg1 admin]$cat listener.ora

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=dsg1)(PORT=1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=orcl)

(ORACLE_HOME=/u01/app/oracle/product/10.2.0)

(PRESPAWN_MAX=20)

(PRESPAWN_LIST=

(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))

)

[oracle@dsg1 admin]$cat tnsnames.ora

orcl =

(DESCRIPTION =

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

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = dsg1)

(SERVICE_NAME =orcl)

)

lcro =

(DESCRIPTION =

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

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = dsg2)

(SERVICE_NAME =lcro)

)

二，创建db_link

语法：

createpublic database link dblink_name connecttousername identified bypasswd using 'tns_name'

说明：

dblink_name ：是dblink名称

username：是远端的用户名,要连接的数据库用户

passwd：是远端的秘码

'tns_name' ：是tnsnames.ora中的连接字符串名称

SQL> createpublic database link lcro connect to scott identified by tiger using 'lcro';

Database linkcreated.

测试创建按的db_link

SQL>conn scott/tiger@lcro

ERROR:

ORA-28001: the password has expired -----由于是第一次链接这个Scott用户

Changingpassword for scott

Newpassword:

Retypenew password:

Passwordchanged

Connected.

查看lcro下scott中的表

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

然后再在原来的数据库上建一张表

SQL>create table t (t varchar2(8));

Tablecreated.

再回到远程查看

SQL>/

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

T TABLE

成功！！！

三，创建物化视图同步数据

1.在源用户上执行以下，创建物化视图日志

对于已定义主键的表，分别执行以下 sql：

create materialized view log on 表名 withprimary key;

对于未定义主键的表，分别执行以下 sql：

creatematerialized view log on 表名 with rowid;

先创建测试表

SQL>create table scott.t as select * from dba_objects;

Tablecreated.

SQL>conn scott/tiger

Connected.

SQL>select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

T TABLE

SQL> creatematerialized view log on t with rowid;

Materialized viewlog created.

2.在目标用户上执行以下 sql，创建物化视图

SQL> create materialized view t_mv refresh fast on demand withrowid as select * from t@lcro; ---lcro为db_link的名字

Materialized viewcreated.

查看创建的物化视图

SQL>show parameter instance_name

NAME TYPE VALUE

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

instance_name string orcl

SQL>select table_name,TABLESPACE_NAME,INSTANCES from user_tables wheretable_name='T_MV';

TABLE_NAME TABLESPACE_NAME INSTANCES

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

T_MV SYSTEM 1

SQL>select count(*) from t_mv;

COUNT(*)

----------

50334

3.测试

在源数据库插入100条数据，在远程进行刷新

SQL> insert intot select * from t where rownum<101;

100 rows created.

SQL> commit;

Commit complete.

SQL>

SQL> selectcount(*) from t;

COUNT(*)

----------

50434

SQL> selectcount(*) from mlog$_t;

COUNT(*)

----------

100

在远程进行刷新：

SQL> execdbms_mview.refresh('t_mv','fast')

PL/SQL proceduresuccessfully completed.

SQL> selectcount(*) from t_mv;

COUNT(*)

----------

50434

源端：

SQL> selectcount(*) from mlog$_t;

COUNT(*)

----------

成功！！！