Friday, July 9, 2010

How to create Oracle Database 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.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

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

Oracle Net must be installed on both the local and remote Oracle databases.

This is how you can create a database link .

        CONNECT TO schema_user IDENTIFIED BY user_password
                        (ADDRESS=(PROTOCOL=TCP)(HOST=<target database host>)(PORT=<target database  port>))
                (CONNECT_DATA=(SID=<target database ORACLE_SID>))


Post a Comment


ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com