A DB link is a pointer that defines on-way communication path from an oracle database server to another database server. It is one way means if you define a link from DB ‘A’ to DB ‘B’ then the database A can access information from database B but by using the same link B cannot access the information from A.
A DB link allows the local user to access data on remote database as if it is a local table’s data. For this connection each database must have unique global name in distributed environment.
Database links are either private or public or global. If they are private, then only the user who created the link has access; if they are public, then all database users have access and if an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network.
The great advantage of database link is that it allows local user can access link to the remote database without having to be a user on remote database.
Creating database link:
To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Syntax:
CREATE [SHARED][PUBLIC] DATABASE LINK link_name [CONNECT TO user IDENTIFIED BY password] [AUTHENTICATED BY user IDENTIFIED BY password] [USING ‘connect_string’]
Restriction: You cannot create a database link in another user’s schema, and you cannot qualify dblink with the name of a schema.
Example:
CREATE Public Database Link HRMS Connect To HRMS Identified By HRMS Using test;SELECT * from [email protected];
Note: Oracle Database uses the global database name to name the schema objects globally using the following scheme: [email protected]_database_name. If GLOBAL_NAMES is set to FALSE, then you can use any name for the link
Features of Different DB link:
Private database link: This link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database.
Public database link: When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database.
Global database link: When an Oracle network uses a directory server, an administrator can conveniently manage global database links for all databases in the system. Database link management is centralized and simple.
REMOTE_OS_AUTHENT Value
SQL> SHOW PARAMETER REMOTE_OS_AUTHENT;
NAME TYPE VALUE
————————– ——— ———–
remote_os_authent boolean FALSE
If it is true then an externally-authenticated user can connect to the remote database using a connected user database link and if it is false then an externally-authenticated user cannot connect to the remote database using a connected user database link unless a secure protocol or a network authentication service supported by the Oracle Advanced Security option is used.
Drop Database link:
DROP [PUBLIC] DATABASE LINK link_name ALTER SESSION CLOSE DATABASE LINK link_name
Note: You cannot drop a database link in another user’s schema and you must specify PUBLIC to drop a PUBLIC database link.
Related Views:
DBA_DB_LINKS: All database links in the database
ALL_DB_LINKS: Database links accessible to the user
USER_DB_LINKS: Database links owned by the user
Database Link Restrictions:
- You cannot perform the following operation using database links:
- Grant Privileges on remote objects.
- Analyze remote objects
- Define or enforce referential integrity
- Grant roles to users in a remote database
- Obtain non-default roles on a remote database
- Execute hash query joins that use shared server connections
- Use a current user link without authentication through SSL, password, or NT native authentication
The Behavior of database link is also depends upon the categories of users involved in creation of DBlink.
Example: Private Connected User:
CREATE DATABASE LINK sales.us.americas.acme_auto.com USING ‘sales_us’;
Example: Private Current User:
CREATE DATABASE LINK foo CONNECT TO CURRENT_USER USING ‘am_sls’;
Example: Private fixed User:
CREATE DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger USING ‘sales_us’;
Example: Public fixed User:
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING ‘rev’;
Example: Shared public fixed User:
CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY anupam IDENTIFIED BY bhide USING ‘sales’;
