Thursday, February 10, 2011

DB Link

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.
Use the following code to create a database link:
create database link "database_link_name"       
connect to username
identified by "password"
using 'connection_name';

The values you need to plugin are:
  • database_link_name the name you want to give your database link and what you enter after the @ in TABLENAME@DBLINK
  • username the username to connect to connect to the database
  • password the password for the username to connect to the database
  • connect_name the TNS or full domain name to uniquely identify the database connection
Important tips:
  • You need to create a new connection to test the database link - it won't work in the same connection used to create the database link.
  • If you get a TNS error, ensure that TNSNAMES.ORA is up to date on the database server you are creating the link on (i.e Both the databases should be updated with the latest TNS).
If the table is in your connected schema.
select * from table_name@dbname;
If the table is not in your connected schema. And you have select privileges on other schema table then.
select * from schema.table_name@dbname ;


Post a Comment