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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.