DB LINK

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services. Here example are given from Oracle database to another Oracle database .

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY password
USING sid_name

In Some situation even though user_name,sid_name and password is correct database link created will result to a failure ;this is because Oracle cannot able to get the connection because of some reason ;in-order to meet this situation we want to specify more details for DBLink creation then this script cam be used.

CREATE DATABASE LINK "dblink_name"
CONNECT TO user_name
IDENTIFIED BY 'password'
USING '(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521)))
(CONNECT_DATA=(SID=sid_name)))';

Errors

1)
By using DBLink DDL operations are not allowed .Such as Create ,Drop ,Truncate etc

Truncate table TABLENAME@DBLINK
ORA-02021: DDL operations are not allowed on a remote database

2)
If a Table which containing LOB datatypes are not able select using dblink

create table test_db ( fild_db clob,num number)

select * from test_db@convert;
ORA-22992: cannot use LOB locators selected from remote tables

Here only selection of datatypes other than LOB are allowed

select num from test_db@convert; --> This Works Fine.

3)

Before dropping a DBLink all transactions should be end else this error will occur.
ORA-02018: database link of same name has an open connection

---------------------------------