Q
Problem solve Get help with specific problems with your technologies, process and projects.

Querying from two different instances

How can I query from two different instances? What kind of problems could I find having more than one instance? Which is the best strategy to design the new instance, knowing that the new instance is not going to be heavily related with the old instance?

To query from two different instance, you will need to set up a database link in one of the instances to the other instance. Sign on to one instance. This will now be referred to as the local instance. The other instance is referred to as the remote instance.

Then create a database link as follows:

CREATE DATABASE LINK db_link CONNECT TO my_user
IDENTIFIED BY my_passwd USING 'remote_db';
Where 'my_user' is the username in the remote database and 'my_passwd' is that user's password. The value for 'remote_db' is the TNS alias you defined in the TNSNAMES.ORA file for the remote database.

To query from a table in the remote instance, you can just put the link name after the table name in the FROM clause, separated by the '@' symbol. For instance:

SELECT sysdate FROM dual@db_link;
These types of queries depend on both instances being up and running, as well as a valid network connection between the two.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close