Database Link in Oracle?

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 PAY_PAYMENT_MASTER@HRMS;

Note: Oracle Database uses the global database name to name the schema objects globally using the following scheme: schema.schema_object@global_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’;

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: