oracle 公共dblink,Oracle private dblink和pubic dblink

DB : 11.2.0.3.0

Oracle DBLINK 创建分为private 和 public dblink，默认创建的为private ; private dblink 只有创建的schema才能删除,sys也删除不了；public dblink 任意schema都可以删除,只要权限够。

一、PRIVATE DBLINK:

收回dba权限：

SQL> revoke dba from yoon;

Revoke succeeded.

尝试连接，无法连接：

SQL> conn yoon/yoon

ERROR:

ORA-01045: user YOON lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

连接sys

SQL> conn / as sysdba

Connected.

授权

SQL> grant connect to yoon;

Grant succeeded.

SQL> grant create database link to yoon;

Grant succeeded.

SQL> conn yoon/yoon

Connected.

当前用户：

SQL> show user

USER is "YOON"

创建dblink：

SQL> create database link dblink_yoon connect to yoon identified by yoon using 'YOON';

Database link created.

连接sys用户：

SQL> conn / as sysdba

Connected.

删除dblink：

SQL> drop database link dblink_yoon;

drop database link dblink_yoon

*

ERROR at line 1:

ORA-02024: database link not found

SQL> show user

USER is "SYS"

SQL> drop database link dblink_yoon ;

drop database link dblink_yoon

*

ERROR at line 1:

ORA-02024: database link not found

查看dblink

SQL> select * from dba_db_links;

OWNERDB_LINK       USERNAME   HOST       CREATED

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

YOONDBLINK_YOON   YOON       YOON       14-NOV-14

连接创建dblink用户:

SQL> conn yoon/yoon

Connected.

SQL> drop database link dblink_yoon ;

Database link dropped.

二、PUBLIC DBLINK:

SQL> conn / as sysdba

Connected.

SQL> grant dba to yoon;

Grant succeeded.

SQL> conn yoon/yoon

Connected.

SQL> show user

USER is "YOON"

SQL> create public database link dblink_yoon connect to yoon identified by yoon using 'YOON';

Database link created.

SQL> conn / as sysdba

Connected.

SQL> grant dba to yoon;

Grant succeeded.

SQL> conn yoon/yoon

Connected.

SQL> show user

USER is "YOON"

SQL> create public database link dblink_yoon connect to yoon identified by yoon using 'YOON';

Database link created.

SQL> conn / as sysdba

Connected.

SQL> select * from dba_db_links;

OWNERDB_LINK       USERNAME   HOST       CREATED

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

PUBLICDBLINK_YOON   YOON       YOON       14-NOV-14

SQL> drop public database link dblink_yoon;

Database link dropped.