If you’ve got two Oracle databases and you want to query the data from one database whilst connected to the other one, you can create a database link between the two. Executing the ‘CREATE DATABASE LINK’ command in the first database will create a schema object in it which allows you to access objects/data in the second one.
In fact, the second database doesn’t even need to be an Oracle database. If you do want to access a non-Oracle database through a database link though you’ll need to use Oracle Heterogeneous Services.
Once you’ve created a database link, you can then use it to access the objects in the second database. You can use INSERT, UPDATE, DELETE and LOCK TABLE commands to access the data in the remote tables and views. You do this by adding the name of the database link to the table or view name in the SQL statement, along with the @ character, for example:
SELECT * FROM REMOTE_TABLE@MY_DB_LINK;
CREATE DATABASE LINK Syntax
The basic syntax for the ‘CREATE DATABASE LINK’ command is as follows:
CREATE [PUBLIC] DATABASE LINK <link_name>
CONNECT TO <user_name>
IDENTIFIED BY <password>
USING ‘<service_name>’;
The PUBLIC clause makes the database link available to all database users. If you don’t specify it, access to the link is private.
Example Command
Below is an example CREATE DATABASE LINK command:
CREATE DATABASE LINK my_db_link
CONNECT TO my_user
IDENTIFIED BY my_password
USING ‘my_service’;
This command will create a private database link to the database whose service name is ‘my_service’. The link will be a private link and the connection will be made using the username/password ‘my_user/my_password’.
To query the data in the MY_TABLE table in the remote database, you could then execute a query such as: SELECT * FROM MY_TABLE@my_db_link;