• Database Link

    A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another.

    The following link types are supported:

    Private database link- belongs to a specific schema of a database. Only the owner of a private database link can use it.
    Public database link- all users in the database can use it.
    Global database link- defined in an OID or Oracle Names Server. Anyone on the network can use it.

    What is a database link?

    A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. A database link connection allows local users to access data on a remote database.

    Prerequisites:

    To create a database link, you must have the CREATE DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

    In the following example:
    There are two databases: DEV and PORTAL
    We are creating a db link on DEV so that we can acces the tables of portal user from DEV database.

    Make TNS Entries on both databases

    dev =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = 192.168.1.106)
    (PORT = 1521)
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = dev)
    )
    )

    Portal_New =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = 192.168.1.115)
    (PORT = 1521)
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = portal)
    )
    )

    Check TNS on both databases

    From portal database
    C:Documents and SettingsAdministrator>tnsping dev

    TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 – Production on 16-SEP-2
    014 23:20:52

    Copyright (c) 1997, 2007, Oracle. All rights reserved.

    Used parameter files:
    C:oracle11gproduct11.1.0db_1networkadminsqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.254.
    0.206) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev))
    )
    OK (10 msec)

    From dev database
    oratst#tnsping portal_new

    TNS Ping Utility for HPUX: Version 10.2.0.3.0 – Production on 17-SEP-2014 12:45:30

    Copyright (c) 1997, 2006, Oracle. All rights reserved.

    Used parameter files:
    /Clone/db03/oracle/10R2/network/admin/DEV_oratst/sqlnet_ifile.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.115) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = portal)))
    OK (110 msec)

    connect to dev database from apps user:
    sqlplus apps/apps

    Creating a Private Database Link:

    create database link db link name connect to username identified by “password” using ‘tns name’;

    In the following statement, user portal on the remote database defines a fixed-user database link named Portal_New to the portal schema on the local database:

    create database link DEVTOPORTAL.192.168.1.106 connect to portal identified by "portal123" using 'Portal_New';

    Once this database link is created, you can query tables in the schema PORTAL on the remote database in this manner:


    SELECT * FROM portal.emp@DEVTOPORTAL.192.168.1.106;

    How to check DB link:

    select * from dba_db_links;

    OWNER DB_LINK USERNAME HOST CREATED
    APPS devtoportal.192.168.1.106 PORTAL portal_new 29-APR-14

    TESTING:

    SQL> select * from dual@‚ÄĚdevtoportal.192.168.1.106";

    D
    -
    X

    For dropping a database link.

    drop database link;


    drop database link DEVTOPORTAL.192.168.1.106;

Skip to toolbar