What is Database link and its types

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Parameters in Init.ora file related to Database Links

Ø  global_names 

Ø  open_links

è Maximum number of open links per session

è Deafult value is 4

è Range of values – 0 to 255

Ø  open_links_per_instance

è Default value is 4

è Range of values – 0 to 4294967295 (4 GB -1)

è In RAC, multiple instances can have different values.

Types of Database Links

Ø  Private:

-   The more secure db link when created in a specific schema, only the owner or the PL/SQL program created within that schema can use this database link to access respective schema objects in the corresponding remote database.

-    By default, a database link will be a private one until and unless “Public” or “Global” are explicitly mentioned while creating the DB link.

Ø  Public:

-      This is a database-wide db link

-     Can be used by all the users and PL/SQL programs in that database to access respective schema objects in the corresponding remote database

-      When many users need access to the same schema in a remote Oracle database, we can create a single public database link for all users in a database. 

Ø  Global:

-     This is a network/global-wide db link

-    Defined in an OID or Oracle Names Server. Anyone on the network can use to access respective schema objects in the corresponding remote database.

-    When an Oracle network uses Oracle Names, an administrator can easily manage global database links for all databases in the system. Database link management is centralized and simple since the changes on OID will reflect the changes while the Client connects to the Server.