oracle创建远程试图,创建远程基表的物化视图

当用户要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的dblink,

通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

在创建dblink之前用户必须有创建dblink的权限。想知道有关dblink的权限，以sys用户登录到本地数据库：

如何查看Global_name参数是true还是False? 当数据库参数global_name=false时，就不要求数据库链接名称跟远端数据库名称一样。

SQL> show parameter global_name;

NAME                          TYPE     VALUE

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

global_names                 boolean     FALSE

SQL> select * from v$option where PARAMETER='Advanced replication';

PARAMETER         VALUE

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

Advanced replication TRUE

SQL> select * from user_sys_privs t where t.privilege like upper('%link%');

USERNAME               PRIVILEGE                ADM

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

SYS                   DROP PUBLIC DATABASE LINK        NO

SYS                   CREATE DATABASE LINK            NO

SYS                   CREATE PUBLIC DATABASE LINK        NO

可以看出在数据库中dblink有三种权限CREATE DATABASE LINK(所创建的dblink只能是创建者能使用，别的用户使用不了)，

CREATE PUBLIC DATABASE LINK(public表示所创建的dblink所有用户都可以使用)和DROP PUBLIC DATABASE LINK。

在以下创建物化视图的例子中，基表是远程数据库所带的HR Schema中的employees表。拥有基表的用户是hr，创建物化视图的用户是george。

==================================================================

创建远程基表的on demand refresh物化视图

==================================================================

物化视图的刷新模式有两种：ON DEMAND和ON COMMIT。

ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同，ON DEMAND指物化视图在用户需要的时候进行刷新，

可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新,即更新物化视图，以保证和基表数据的一致性;

而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

创建用户并授予权限

SQL> create user george identified by george;

User created.

SQL> grant connect, resource to george;

Grant succeeded.

SQL> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to george;

Grant succeeded

要创建dblink首先在本地数据库tnsnames.ora文件中配置了要远程访问的数据库服务别名。

接下来创建dblink

SQL> create public database link hr_remote connect to hr identified by hr using 'dg01prmy';

Database link created.

其中hr_remote是你创建的dblink名字，dg01prmy是远程数据库的实例名，hr/hr是登录到远程数据库的用户/密码。

然后在本地数据库中通过dblink访问远程数据库'hr_remote'中hr.employees表，sql语句如下所示

SQL> select * from hr.employees@hr_remote;

SQL> select count(*) from employees@hr_remote;

COUNT(*)

----------

107

在远程数据库上创建物化视图日志

视图的刷新将采用增量刷新的方式，为配合增量刷新，ORACLE要求要在基表上建立物化视图日志。

SQL> conn hr/hr

SQL> create materialized view log on employees with primary key including new values;

创建物化视图

SQL> create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from employees@hr_remote;

create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from employees@hr_remote

*

ERROR at line 1:

ORA-01031: insufficient privileges

创建物化视图需要的权限 以sysdba授权

SQL> grant create any materialized view to george;

Grant succeeded.

SQL> conn george/george

Connected.

注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。

SQL> create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from employees@hr_remote;

create materialized view mv_employees build immediate refresh fast with primary key on commit as select * from employees@hr_remote

*

ERROR at line 1:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL> create materialized view mv_employees build immediate refresh fast with primary key as select * from employees@hr_remote;

Materialized view created.

SQL> select count(*) from mv_employees;

COUNT(*)

----------

107

Tips：

物化视图的刷新

(1)使用dbms_mview.refresh 手工刷新

如：

EXEC DBMS_MVIEW.REFRESH('mv_employees');

--完全刷新

EXEC DBMS_MVIEW.REFRESH(LIST => 'mv_employees',METHOD => 'C');

EXEC DBMS_MVIEW.REFRESH(LIST => 'mv_employees',METHOD => 'C');

EXEC DBMS_MVIEW.REFRESH('mv_employees','C');

--快速刷新

EXEC DBMS_MVIEW.REFRESH(LIST => 'mv_employees',METHOD => 'F');

EXEC DBMS_MVIEW.REFRESH('mv_employees','F');

(2)使用dbms_refresh.refresh 过程来批量刷新MV

如果我们在创建物化视图的过程指定start 和next time的刷新时间，那么Oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。