Oracle dblink

Using Dblink to connect to other database

Dblink, is bridge to connected databases.

Creating:

CREATE [PUBLIC] [PRIVATE][SHARED]DATABASE LINK dblink_name

Connect to target_user_name,

İdentified by Target_user_password

USING ’tns_name’;

For example:

CREATE SHARED PUBLIC DATABASE LINK veysi_lnk
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY sec_usr IDENTIFIED BY sec_pass
USING 'DB_ORCL';

If we want to create dblink we have to have some privileges such as:

  CREATE DATABASE LINK ,  CREATE PUBLIC DATABASE LINK

Also we can create dblink by Current user . Current user, should be the same with Remote database user and dblink user. That’s why user should be global

CREATE PUBLIC DATABASE LINK remote_connect

CONNECT TO CURRENT_USER

USING ‘DB_ORCL’;

For example:

CREATE PRIVATE DATABASE LINK remote_connect

CONNECT  TO veysi IDENTIFIED BY veysi

USING ‘DB_ORCL’;

Check your tnsname.ora :

DB_ORCL = (DESCRIPTION = (ADDRESS =
             (PROTOCOL = TCP) (Host = dborcl.localdomain) (Port = 1521))
             (CONNECT_DATA = (SID = db_orcl) (GLOBAL_NAME = db_orcl.localdomain.com)
             (SERVER = DEDICATED)))

When creating Dblink is requisite GLOBAL_NAME

So check your global name

SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
-----------
db_orcl.localdomain

Now creating by global_name dblink;

CREATE PUBLIC DATABASE LINK db_orcl.localdomain

CONNECT  TO VEYSI IDENTIFIED BY  VEYSI

USING ‘DB_ORCL’;

Creating a view;

CREATE VIEW vw_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Or creating a table;

CREATE TABLE tbl_vey_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Updating

UPDATE employees@remote_connect

   SET salary=salary*1.1

   WHERE last_name = 'AYSE';

INSERTING

Kullanımı:

INSERT INTO employees@remote_connect(salary)

VALUES(salary*1.1);

   WHERE last_name = 'AYSE';

DELETING 

DELETE FROM employees@remote_connect ;

   WHERE employee_id = 150;

SELECT:

SELECT * from  employees@remote_connect ;

Looking at DBLINKS

SELECT * FROM all_db_links ;

or

SELECT * FROM dba_db_links; 

Dropping

DROP DATABASE LINK dblink_name

To close Dblink

ALTER SESSION CLOSE DATABASE LINK remote_connect;