Oracle private dblink…

最新推荐文章于 2023-04-26 15:28:49 发布

Hank丶Yoon

最新推荐文章于 2023-04-26 15:28:49 发布

阅读量409

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/yoon0205/article/details/50605497

Oracle 专栏收录该内容

70 篇文章

订阅专栏

本文详细介绍了Oracle DBLink的创建与管理过程，包括private和public两种类型的区别，以及如何进行权限操作、连接和删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

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.