oracle database link

[size=small]目前我的数据库里只有tinadb一个实例，要创建db link 需要再创建一个库，
因为主机资源有限，我就在本地创建了。db link也同样适用于连接远程数据库，只需要对应ip即可

1.dbca新建库sab

先打开xmanager passive

[oracle@oratest ~]$ cd /u01/oracle/bin/
[oracle@oratest bin]$ export DISPLAY=192.168.10.235:0.0 --启动图形化界面
[oracle@oratest bin]$ dbca

配置监听
[oracle@oratest rdbms]$ cd /u01/oracle/network/admin/
[oracle@oratest admin]$ vi listener.ora
tinadb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1521))
)
)
SID_LIST_tinadb =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= tinadb)
(SID_NAME = tinadb)
(ORACLE_HOME = /u01/oracle)
)
)
sab =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1522)) --端口不能重复
)
)
SID_LIST_sab =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= sab)
(SID_NAME = sab)
(ORACLE_HOME = /u01/oracle)
)
)

启动监听lsnrctl start sab

2.database link作用
database link，它是为了更方便的在一个数据库中访问另一个数据库（包括本地和远程的，道理是一样的）而创建的一个连接字符串。

3.授权用户创建db link

1）如果是用sys用户或者拥有dba角色的用户，就不需要授权
SQL> select ROLE,PRIVILEGE,ADMIN_OPTION from role_sys_privs where role='DBA' and PRIVILEGE like '%LINK%';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA DROP PUBLIC DATABASE LINK YES
DBA CREATE DATABASE LINK YES
DBA CREATE PUBLIC DATABASE LINK YES

2）普通用户:grant create public database link,create database link to username;

4.创建db link

1）我们先在sab库中创建一个db link 连接到tinadb库

create public database link "lk_sab_tinadb" connect to "tina" identified by "tina"
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME =tinadb)))';

验证db link的连通性：
select 'x' from dual@lk_sab_tinadb;

2）查看db link
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
---- -------------------
PUBLIC LK_SAB_TINADB

或者
select * from dba_db_links;

3）使用db link
SQL> select * from tina.t1@lk_sara_tinadb;
ID
----------
1
2

SQL> create table ts as select * from tina.t1@lk_sab_tinadb;
SQL> select * from ts;
ID
----------
1
2

4）我们也可以建立一个私有的db link，这次我们在tinadb中创建一个，连接到sab库中(用户suser是sab库中用户，表s1是sab中suser的表）
[oracle@oratest admin]$ sqlplus sys/tina@tinadb as sysdba
SQL> conn tina/tina@tinadb
Connected.
SQL> create database link "lk_tinadb_sab" connect to "suser" identified by "suser"
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.253)(PORT = 1522)))(CONNECT_DATA =(SERVICE_NAME =sab)))';

Database link created.

SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
------- -----------------------
TINA LK_TINADB_SAB

SQL> select * from suser.s1@LK_TINADB_SAB;

ID NAME
---------- ----------
1 test
2 haha
3 heihei

SQL> insert into suser.s1@lk_tinadb_sab values(4,'youyou');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from suser.s1; ---sab库中就直接被修改了。

ID NAME
---------- ----------
4 youyou
1 test
2 haha
3 heihei


5.删除db link
drop public database link dblinkname;

6.获取已创建db link的执行语句
SQL> select dbms_metadata.get_ddl('DB_LINK','LK_SAB_TINADB','PUBLIC') from DUAL;
SQL> select dbms_metadata.get_ddl('DB_LINK','LK_TINADB_SAB','TINA') from dual;

DBMS_METADATA.GET_DDL('DB_LINK','LK_TINADB_SAB','TINA') ----三个字段分别表示object_type，linkname，link owner
--------------------------------------------------------------------------------

CREATE DATABASE LINK "LK_TINADB_SAB"
CONNECT TO "suser" IDENTIFIED BY VALU ---被截断了

7.授权其他用户使用tina用户的私有db link

私有的DB Link，其它用户是不能用的，Oracle没有关于dblink的select或者execute权限。
必须转换才能实现， 如下例子：

SQL> conn tina/tina@tinadb
Connected.
SQL> create view tina.s1 as select * from suser.s1@lk_tinadb_sab; ---创建一个视图
View created.
SQL> grant select on tina.s1 to test1; --授权视图的select权限给test1
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> select * from tina.s1;
ID NAME
---------- ----------
4 youyou
1 test
2 haha
3 heihei

[/size]