Oracle cross-database query (database link)

Oracle cross-database query (database link)

How to Create a database link for cross-database Oracle Query

Grant permissions to the dbuser account on the local Oracle Database

SQL> grant create database link to dbuser;

Configure the tnsnames. ora file of the Local Database Server
$ Vi $ ORACLE_HOME/network/admin/tnsnames. ora

Add the connection configuration that requires remote connection to the server, such:

ORCL_REMOTE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = oradb) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Log on to the local database and create the database link

Execute the following query statement. ORCL_LINK is the database link name (customizable), and ORCL_REMOTE is the connection name defined in tnsnames. ora,
Dbuser is the user name and password

Create database link ORCL_LINK connect to dbuser identified by password using 'orcl _ remod ';

Query the method of creating database link 2:
1) Execute SQL statements.
Select * from user_db_links; -- User DB Link
Select * from dba_db_links; -- dba DB Link
Select * from v $ dblink; -- current DB Link

2) In PL/SQL, click database links in the browser on the left to view the database link.

Use connected database

Querying, deleting, and inserting data are the same as operating a local database, except that the table name must be written as "table name @ database link name", as shown in figure
Select * from table_name @ ORCL_LINK

Others:

Delete database link (ORCL_LINK in this example)
SQL> Drop database link ORCL_LINK;

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian