The type of database links that oracle lets you create are private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database.
A private link creates a link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.
The syntax for creating a private database link is:
CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
e.g.
CREATE DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';
If the above statement is created in scott schema of the local database then only scott user of the local database can use the database link.
A public link creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.
The syntax for creating a public database link is:
CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
e.g.
CREATE PUBLIC DATABASE LINK db_orders CONNECT TO scott IDENTIFIED BY tiger USING 'orders_us';
All the users in the database inclusive of the user scott can use the database link.
A Global database link creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. To create Global database links we must ensure that:
§ Global database link names must match database global names
§ Your GLOBAL_NAMES initialization parameter should be set to TRUE
§ You must enable the plug and play setting for your listeners
The initialization parameter file should contain the following entries:
DB_NAME = <DB NAME>
DB_DOMAIN = <DOMAIN NAME>
GLOBAL_NAMES = TRUE
USE_PLUG_AND_PLAY_LISTENER = ON
CREATE DATABASE LINK <database link name> USING '<database name>'; |
When you create a database link, you determine which user should connect to the remote database to access the data. There are three different types of users and they are:
§ Connected user
§ Current user
§ Fixed user
This link always connects as the currently connected user. This requires the user using the link to have an ID in both databases. When using a connected user link, your access is in the remote database is defined by the privileges of that ID in the remote database.
CREATE DATABASE LINK <database link name> USING '<remote database>'; |
e.g.
CREATE PUBLIC DATABASE LINK TEST USING 'BEADMISC';
A global user in a CURRENT_USER database link. The global user must be authenticated by an X.509 certificate (an SSL-authenticated enterprise user) or a password (a password-authenticated enterprise user), and be a user on both databases involved in the link.
Current user links are an aspect of the Oracle Advanced Security option.
CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO CURRENT_USER using '<remote database>'; |
This is the normal database link were we provide the credential of the remote database when we create the database link.
CREATE DATABASE LINK <database link name> CONNECT TO <remote user> IDENTIFIED BY <remote user password> USING ‘<Service Name>'; |
The privileges required to create, drop, alter database link are:
§ Create database link
§ Create public database link
§ Alter database link
§ Drop database link
§ Alter public database link
§ Drop public database link
The important views that can be used to monitor database links are:
§ ALL_DB_LINKS
§ DBA_DB_LINKS
§ USER_DB_LINKS
§ V$DBLINK
§ GV$DBLINK
The views ALL_DB_LINKS, DBA_DB_LINKS, USER_DB_LINKS lists all database links for the connected user, whole database and schema owner.
The views V$DBLINK and GV$DBLINK show the database link connections that are currently open in your current session:
CREATE DATABASE LINK test_db_link CONNECT TO
eserv IDENTIFIED BY vre10g USING 'beadmisc';
Step 1:
Access the remote database using the link:
select * from dba_db_links;
Step 2:
select * from v$dblink;
The output will be displayed as in the below screen shot.
Some of the important columns are:
DB_LINK | The name of the database link. |
HETROGENEOUS | If yes that means the database link is pointing to a database other than oracle. |
OPEN_CURSORS | Number of remote connections established or queries that are executed. |
IN_TRANSACTION | If Y it means the transaction has not been committed. |
LOGGED_ON | Is the current session open. |
Step 3:
Issue a COMMIT;
Run the below query:
select db_link,in_transaction from v$dblink;
You will see that in_transaction column has value of NO.
If you access a database link in a session, then the link remains open until you close the session. For example:
§ If 5 users/ schemas access the same public link in a local database, then 5 database link connections are open are open in the remote database.
§ If 5 users/ schemas and each user access a private link, then 5 database link connections are open.
§ If one user accesses 5 different links, then 5 database link connections are open.
When you close a session, the links that were active in the session are automatically closed.
To close the database link manually please execute the below command.
ALTER SESSION CLOSE DATABASE LINK <database link name>; |
Note that this statement only closes the links that are active in your current session and the user/schema must have the alter session privilege.
The statement to drop a database link is given below:
DROP DATABASE LINK <database link name>; |
You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions.
The default value for OPEN_LINKS is 4. If OPEN_LINKS is set to 0, then no distributed transactions are allowed.
To view the OPEN_LINKS value executes the query below:
select * from v$parameter where upper(name) ='OPEN_LINKS'
The changes can be made to the init<db_name>.ora or can be changed dynamically by the DBAs.
After you have configured the necessary database links, you can use various tools to hide the distributed nature of the database system from users. In other words, users can access remote objects as if they were local objects. To create local transparency we can use
§ Views
§ Synonyms
§ Procedures
Syntax using synonym:
CREATE synonym <synonym_name> FOR [schema.]object_name[@database_link_name]; |
e.g.
CREATE SYNONYM test_syn FOR scott.emp@db_link_test;
Similarly we can create synonyms for packages and procedures in the remote database and use them as our local copy.
The following operations cannot be used using database links:
1. Grant privileges on remote objects.
2. Execute DESCRIBE operations on some remote objects except for the following objects:
§ Tables
§ Views
§ Procedures
§ Functions
3. Analyze remote objects.
4. Define or enforce referential integrity.
5. Grant roles to users in a remote database.
6. Execute hash query joins that use shared server connections.
7. Use a current user link without authentication through SSL, password, or NT native authentication.
A database must guarantee that all statements in a transaction, distributed or non-distributed, either commit or roll back as a unit. The effects of an ongoing transaction should be invisible to all other transactions at all nodes; this transparency should be true for transactions that include any type of operation, including queries, updates, or remote procedure calls.
The general mechanisms of transaction control in a non-distributed database are discussed in the Oracle Database Concepts. In a distributed database, the database must coordinate transaction control with the same characteristics over a network and maintain data consistency, even if a network or system failure occurs. The database two-phase commit mechanism guarantees that all database servers participating in a distributed transaction either all commit or all roll back the statements in the transaction. A two-phase commit mechanism also protects implicit DML operations performed by integrity constraints, remote procedure calls, and triggers.
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. The database link provides local transparency by creating synonyms for tables, packages and procedures in the remote database. The user can access these objects just as there local objects. Overall database links provide lot of flexibility in distributed computing they must be used judiciously to gain most advantages.
No comments:
Post a Comment