Oracle Database Link

When working in one database you can make reference to data in another database by creating a database link.

To create an Oracle database link, the quickest way is to just create a public database link using a user id in the other database that has permissions to reference the data you need. {LINKNAME} = the name for the link you want to create, {LINKUSER} = the user on the remote database that has access, and {LINKUSERPASSWORD} is that users password.  {TNSNAMES-DB-ENTRY} is the database entry name from the TNSNAMES.ora file.

create public database link {LINKNAME} connect to {LINKUSER} identified by {LINKUSERPASSWORD using ‘{TNSNAMES-DB-ENTRY}’;

To Reference the data from the linked database, simply add the link identifier to the table you are selecting – for example:

select * from remotetable@{LINKNAME};

To Remove the database link:

drop public database link {LINKNAME};