ROLE OF GLOBAL NAMES IN DATABASE LINKS
Introduction:-
This is mainly for Oracle DBA’S who are widely seen about Database links in their organizations, Let us understand closely about the concept and the role of Global names in database links.
Definition:-
A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.
Why Use Database Links?
The great advantage of database links is that they allow users to access another user’s objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.
Global Database Names in Database Links
Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by DB_NAME initialization parameter.
Typically, a database link has the same name as the global database name of the remote database that it references, For example, if the global database name of a database is sales.us.example.com, then the database link is also called dblinkname.us.example.com
When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remoted database. For example, if the global database name for hq is hq.example.com, and GLOBAL_NAMES is TRUE, then the link name must be called dblink.example.com. Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file.
If you set the initialization parameter GLOBAL_NAMES TO FALSE, then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to foo.example.com as foo.
In that case, both ‘foo.example.com’ and ‘foo’ can be used as database link.
After you have enabled global naming, database links are essentially transparent to users of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database sales:
CREATE PUBLIC DATABASE LINK sales.division3.example.com USING ‘sales 1’;
DBA_DBA_LINKS provides the information about all the database links in the database.
- To Create Public Database Link
The following command creates a public database link named prod_pub_link using local net service name to_user_prod”
SQL> CREATE PUBLIC DATABSE LINK PROD_PUB_LINK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘TO_USER_PROD’;
2.To Create Private Database Link
The following command creates a private database link named prod_priv_link using local net service name ‘to_user_prod’;
SQL> CREATE DATABASE LINK PROD_PRIV_LINK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘TO_USER_PROD’;
- To access emp table date under scott schema which Is in remote database
- a) Connect to the local database
b) Select data from remote database using database link
SQL> SELECT * FROM SCOTT.EMP@PROD_PUB_LINK;
- To list all the database links information
SQL> SELECT * FROM DBA_DATA_LINKS;
- To check if a database link is PRIVATE OR PUBLIC
SQL> SELECT DB_LINK,OWNER FROM DBA_DB_LINKS;
DB_LINK OWNER
———— ——–
MY LINK 1 SCOTT
MY LINK 2 PUBLIC
Note:-
If the database link is public link when the owner column shows as PUBLIC.
If the link is private link, then the owner columns shows the name of the owner who created the link.
