Oracle database link

A database link is a connection from the Oracle database to another remote database. The remote database can be an Oracle Database or any ODBC-compliant database such as SQL Server or MySQL.

To create a private database link, you use the CREATE DATABASE LINK statement as follows:

CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING ‘remote_database’;

To create a public database link, just add the PUBLIC keyword:

CREATE PUBLIC DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING ‘remote_database’;

Example:

SALES =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.100.143)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SALES_PRD)
)
)

CREATE DATABASE LINK sales CONNECT TO bob IDENTIFIED BY Abcd1234 USING ‘SALES’;

Oracle Database Link best practices

Here are some best practices using the database links:

1. Naming convention: the name of the database links should reflect the nature of the data, not the database server. For example, instead of naming a database link SALES_PRD, you name it as SALES.
2. Remote database users: you should create a user dedicated to a database link. In addition, you should not give this user to anyone else. If you don’t follow this, the database will not work when someone changes the password of the user or even delete it.
3. Use a service-specific entry in the tnsnames.ora instead of the database-specific alias so that you copy between product, test, and development environments, you don’t have to recreate the database link.

Posted from: https://www.oracletutorial.com/oracle-administration/oracle-create-database-link/

The SCOTT/TIGER user exists in two databases, BOSTON_DB and DALLAS_DB, in two different locations.
Each database has a tnsnames.ora file defining DALLAS_DB as a service name.

Examine this command:
CREATE DATABASE LINK dblink1 CONNECT TO scott IDENTIFIED BY tiger USING ‘dallas_db’;
How do you execute the command so that only SCOTT in BOSTON_DB can access the SCOTT schema in DALLAS_DB?

• A. as SCOTT in DALLAS_DB
• B. as SCOTT in BOSTON_DB
• C. as SCOTT in BOSTON_DB and SYS in DALLAS_DB
• D. as SYS in both the databases
• E. as SCOTT in both the databases