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;
OWNER DB_LINK USERNAME HOST CREATED
---------- ------------- ---------- ---------- ---------
YOON DBLINK_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;
OWNER DB_LINK USERNAME HOST CREATED
---------- ------------- ---------- ---------- ---------
PUBLIC DBLINK_YOON YOON YOON 14-NOV-14
SQL> drop public database link dblink_yoon;
Database link dropped.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28939273/viewspace-1332640/ ，如需转载，请注明出处，否则将追究法律责任。
