Database Link and Public Database Link

Database link is a useful thing that I use time to time mostly to compare small tables among databases. We have both way (master-master configuration) replication in place, so sometimes, I need to check few things after small release to make sure some tables are in sync.

I thought, it is worth writing few words about this.

What is database link?

Officially, a database link is a schema object in one database that enables access to other objects on another database. I would like to say - it is kind of 'a connection cable' that establish connections between databases.

Side rule: If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.

In SQL statements in source database, we can refer to a table or view on the other destination database by appending @dblink_name (at sign then db link name) to the table or view name.

How to create database link?

For example, we want to access a destination database which is known as DX01.

CREATE DATABASE LINK DX01.DOMAIN.NET CONNECT TO prod IDENTIFIED BY xyz USING 'DX01';

Here,
* DX01.DOMAIN.NET is the db link name
* prod is the destination schema and xyz is the pass to access that. So we would give access through 'prod' schema on destination server.
* DX01 is the service name for the destination database, which should be found in tnsnames.ora file on the source database.

Now, I can compare tables and also issue other commands to access tables on DX01 remote database using the database link.

SQL> SELECT COUNT(*) FROM LOGIN@DX01.DOMAIN.NET;

Also, to compare tables if those are in sync or not -

SQL> SELECT * FROM LOGIN
MINUS
SELECT * FROM LOGIN@DX01.DOMAIN.NET;



Who owns the database link?

By default, with the create syntax shown above, the database user who is creating database link will own this link - no other user will be able to use this.

To get information of the existing database links in a database, need to query the following view -

SQL> SELECT * FROM DBA_DB_LINKS;



Public database link

There may be cases where I want to create only one database link and would like to use the link for all the schema in source database. Public database link is the answer. The PUBLIC key word is specified to create a public database link available to all users.

CREATE PUBLIC DATABASE LINK DX01.DOMAIN.NET USING 'DX01';

Now, for example, HR schema can use this link, PROD schema can use this link and others too in source database.

Side note: To create a public database link, we will need the CREATE PUBLIC DATABASE LINK system privilege.