Oracle private Dblink and pubic dblink

db:11.2.0.3.0


Oracle DBLINK is created into private and public DBLINK, which is created by default as private; Private Dblink only the created schema can be deleted, SYS can not be deleted, public dblink any schema can be deleted, as long as the permissions are sufficient.

First, PRIVATE DBLINK:
Recover DBA Authority:
Sql> revoke DBA from Yoon;

Revoke succeeded.

Attempt to connect, unable to connect:
Sql> Conn Yoon/yoon
ERROR:
Ora-01045:user YOON lacks CREATE SESSION privilege; Logon denied

Warning:you is no longer connected to ORACLE.

Connect SYS
Sql> Conn/as SYSDBA
Connected.

Authorized
Sql> Grant Connect to Yoon;

Grant succeeded.

Sql> Grant CREATE DATABASE link to Yoon;

Grant succeeded.

Sql> Conn Yoon/yoon
Connected.

Current User:
Sql> Show User
USER is "YOON"

Create Dblink:
Sql> CREATE DATABASE link Dblink_yoon connect to Yoon identified by Yoon using ' Yoon ';

Database Link created.

Connect the SYS User:
Sql> Conn/as SYSDBA
Connected.

Delete 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

View Dblink
Sql> select * from Dba_db_links;

OWNER db_link USERNAME HOST CREATED
---------- ------------- ---------- ---------- ---------
YOON Dblink_yoon YOON YOON 14-nov-14

Connect Create Dblink User:
Sql> Conn Yoon/yoon
Connected.

Sql> drop database link dblink_yoon;

Database Link dropped.

Second, 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.

Oracle private Dblink and pubic dblink