Shared database links

Shared database links are there since Oracle 9iR2, but strangely I have never seen their existence until a customer asked me how to identify in DBA_DB_LINKS (or ALL_) if a database link has been created shared or not…

First, shared database links aim is to reduce number of connection to remote database by sharing same connection link between source and remote by multiple users’ sessions. This can also be seen as a JDBC connections pooling to concentrate multiple session and limit number of real connections onto the database (to obviously save CPU and memory).

Note this functionality is usable whatever you are in dedicated or shared server SQL*Net configuration.

Example of creation:

SQL> CREATE shared DATABASE LINK test2
  CONNECT TO yjaquier IDENTIFIED BY password
  authenticated BY yjaquier IDENTIFIED BY password;
 
DATABASE LINK created.

Then after issue is to know how has been created this database link (the private or public database links are easy to identify with OWNER column), so started from the initial definition of the DBA_DB_LINKS:

SELECT u.name, l.name, l.userid, l.host, l.ctime
FROM sys.LINK$ l, sys.USER$ u
WHERE l.owner# = u.USER#;

And if you construct something like:

SELECT u.name, l.*
FROM sys.LINK$ l, sys.USER$ u
WHERE l.owner# = u.USER#
ORDER BY u.name

Columns AUTHUSR et AUTHPWD are different from NULL, column FLAG is equal to 3 so here is the difference even if it is not documented by Oracle…

Remark:
As explained in official documentation make sure many connections will be initiated using this shared database link unless this may have lead to more network connections…

About Post Author

5 thoughts on “Shared database links

  1. Hi Yannick,

    My requirement is DBlink creation from Oracle Database to mysql database.

    Oracle DB is on Solaris SPRC and Mysql is on windows. Please share me some information or documents on this case.

    It is very urgent requirement.

    Thanks

  2. Hi, if the shared db link is used in a synonym the commit does not seem to release the session created in the remote database. Can you please give me some more info with regard to using shared dblinks?

    thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>