Query table from another ORACLE database

Question

I have two different data base, one is DEVORADB which i use for development, and another one is UATORADB which tester use for testing. UATORADB have the most updated data which is not in development. I want to query tables from UATORADB database in DEVORADB. I was writing in DEVORADB in such a way but not getting the result:

SELECT * FROM TABLE_NAME@UATDEVORADB.

Answer 1

For Oracle,

CREATE DATABASE LINK ...

e.g.

With a database link created and tested, you can do a query (of the style you showed) to retrieve rows from a remote database.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

FOLLOWUP

NOTE: In Oracle, the term "database" refers to the datafiles and logfiles associated with an Oracle "instance". To retrieve data from a second "database" means you need a second connection to the other database. Oracle provides a facility called a "database link". That allows a session(connection) to one database instance to connect to another database instance. (Without this facility, a client would need to create two separate connections, and would need to query the two databases separately.)

If this question is regarding querying from two separate "schemas" within the same database, as long as the user has sufficient privileges on objects in the second schema, the identifier can be qualified with the name of the schema, e.g.

SELECT * FROM UATDEVORADB.TABLE_NAME

To access data on a separate database, a database link can be used...

CREATE DATABASE LINK UADEVORADB 
  CONNECT TO user 
  IDENTIFIED BY password
  USING 'uadevoradb' ;

(This will require an appropriate matching entry in the tnsnames.ora file on the Oracle server, or the oracle names server, or the connection details can be spelled out in place of a tnsnames.ora entry, something like:

CREATE DATABASE LINK UADEVORADB
  CONNECT TO user IDENTIFIED BY password 
  USING '(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=uadevorahost1)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=uadevoradb.domaindb)))'

If the "user" specified in the database link differs from the "owner" of the table on the remote system, and there's no synonym that references the table, the table identifier will need to be qualified with the owner...

SELECT * FROM OWNER.TABLE_NAME@UADEVORADB ;