当用户要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中必须创建了远程数据库的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 的方式。
通过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 的方式。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29960937/viewspace-1408603/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29960937/viewspace-1408603/