Oracle private dblink…

DB :11.2.0.3.0


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

一、PRIVATEDBLINK:
收回dba权限：
SQL> revoke dba from yoon;

Revoke succeeded.

尝试连接，无法连接：
SQL> conn yoon/yoon
ERROR:
ORA-01045: user YOON lacks CREATESESSION privilege; logon denied

Warning: You are no longer connectedto ORACLE.

连接sys
SQL> conn / as sysdba
Connected.

授权
SQL> grant connect toyoon;

Grant succeeded.

SQL> grant create database link toyoon;

Grant succeeded.

SQL> conn yoon/yoon
Connected.

当前用户：
SQL> show user
USER is "YOON"

创建dblink：
SQL> create database linkdblink_yoon connect to yoon identified by yoon using'YOON';

Database link created.

连接sys用户：
SQL> conn / as sysdba
Connected.

删除dblink：
SQL> drop database linkdblink_yoon;
drop database linkdblink_yoon
*
ERROR at line 1:
ORA-02024: database link notfound

SQL> show user
USER is "SYS"
SQL> drop database linkdblink_yoon ;
drop database linkdblink_yoon
*
ERROR at line 1:
ORA-02024: database link notfound

查看dblink
SQL> select * fromdba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------- -------------------- ---------
YOONDBLINK_YOON YOON YOON14-NOV-14

连接创建dblink用户:
SQL> conn yoon/yoon
Connected.

SQL> drop database linkdblink_yoon ;

Database linkdropped.

二、PUBLICDBLINK:

SQL> conn / assysdba
Connected.
SQL> grant dba to yoon;

Grant succeeded.

SQL> connyoon/yoon
Connected.
SQL> show user
USER is "YOON"
SQL> create public database linkdblink_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 linkdblink_yoon connect to yoon identified by yoon using'YOON';

Database link created.

SQL> conn / as sysdba
Connected.

SQL> select * fromdba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
---------- ------------- -------------------- ---------
PUBLICDBLINK_YOON YOON YOON14-NOV-14

SQL> drop public database linkdblink_yoon;

Database link dropped.