ARTICLE: Linking Oracle databases together via database links

This entry describes the process of linking two remote Oracle databases via a remote Oracle client. The process is known in Oracle as Database Links. Once databases are linked, tables in the source database server can be accessed on the destination database server.

Assumptions

• The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the database linking has been completed, the source database tables can be accessed when connected to the destination database.
• That the source, destination and client database software are installed correctly and in working order.
• Readers have a basic understanding of SQL and the dialects used by the above vendor.
• Oracle command usages coloured blue, examples coloured green.

Environment

• Source and destination server version / platform: 64-bit Oracle Enterprise Server 11g r2 on 64-bit Red Hat Enterprise Linux v5 r2. 
• Client version / platform: 64-bit Oracle Client 11g r2 on 64-bit Windows 7 Professional.
• Settings for Oracle are added / modified via the use of a command line client (SQLPLUS) connected only to the destination server. The tnsnames.ora should be modified using your favourite text editor.
• The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.

Oracle - Database Links

1. The clients tnsnames.ora file must be updated with entries for both the source and destination databases.
2. In order to create the (uni-directional) database link from the Oracle destination database to the Oracle source database, the destination database must have an entry in it's tnsnames.ora file for the source database. If the destination database does not said entry, it will need to have its said details supplied during the creation of the database link - ensure this entry exists.
3. On the source database, create the source user used in the database link. Usage: CREATE USER [username] IDENTIFIED BY [password];. Example: CREATE USER srcUser IDENTIFIED BY srcUser;
4. On the source database, grant permissions to the source user. Usage: GRANT CONNECT, RESOURCE, DBA TO [username];. Example: GRANT CONNECT, RESOURCE, DBA TO srcUser;.
5. On the destination database, create the destination user who will be logged into SQLPLUS to create the database link and who will also have read/write access to the destination database. Usage: CREATE USER [username] IDENTIFIED BY [password];. Example: CREATE USER destUser IDENTIFIED BY destUser; 
6. On the destination database, grant permissions to the destination user. Usage: GRANT CONNECT, RESOURCE, DBA TO [username];. Example: GRANT CONNECT, RESOURCE, DBA TO destUser;.
7. On the destination database, logged in to the destination database as the above destUser, create the uni-directional database link. Usage: CREATE DATABASE LINK [source_database_link_name] CONNECT TO srcUser IDENTIFIED BY srcUser USING '[oracle_sid|tnsname_connection_details]';. Example: CREATE DATABASE LINK sourceDBLink CONNECT TO srcUser IDENTIFIED BY srcUser USING 'ORA11GU'; 
8. Finally, to access the source database table via a connection to destination database, use the client to connect to the destination database execute the following. Usage: SELECT * FROM [user].[table_name]@[src_database_link];. Example: SELECT * FROM MyUser.MyTable@sourceDBLink;