Oracle 10g 11g Shared Database – DB Link – Features, Advantages and Disadvantages

advertisements

_____________________________________________________________________________________________________________________

Shared database link help you to limit the network connections between the local and remote servers. For a shared database link need 10 shared server processes in local database and also it requires upto 10 network connections to the remote server.
A non shared database link is preferable in a situation where a user require only one network connection. Shared database links are applicable for the database links where multiple users accessing the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema

How to Create a Shared Database Link?

Share database link creation script must require an AUTHENTICATED BY clause. The schema mentioned in the authenticated by clause must exists in the remote database and it should have CREATE SESSION privilege. These credentials are required to protect the remote shared server processes from clients that masquerade as a database link user and attempt to gain unauthorized access to information. After a connection is made with a shared database link, operations on the remote database proceed with the privileges of the CONNECT TO user or CURRENT_USER, not the AUTHENTICATED BY schema.

Syntax:

CREATE SHARED DATABASE LINK dblink_name

[CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER]

AUTHENTICATED BY schema_name IDENTIFIED BY password

[USING 'service_name'];

Example

CREATE SHARED DATABASE LINK DB_READONLY

CONNECT TO READONLY IDENTIFIED BY READONLY

AUTHENTICATED BY READONLY IDENTIFIED BY READONLY

USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PRODDB)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))'

_____________________________________________________________________________________________________________________

Website Stats