Why db link is used in oracle

The name of db link is required to access data from remote database. Step 2 : Connect tp clause is very important clause which gives user access to connect to remote database name. User should know the password of target database. If you specify only the database name, Oracle will append the database domain to the connect string to form a complete service name.

Create database link in that case ,. The key use of database link is it allows the user to access multiple objects from remote database and to access those there are few standard syntax. As example if you want to access the table from remote db from local database kindly follow following syntax. We already created the database link on ComplexSQL user. After a link is created, you can use it to specify schema objects in SQL statements.

A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.

When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities:. A shared database link can exist in any of these four configurations.

Shared links differ from standard database links in the following ways:. Different users accessing the same schema object through a database link can share a network connection. When a user needs to establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server.

The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.

When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher. 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. To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name.

For example, Figure illustrates a representative hierarchical arrangement of databases throughout a network. The name of a database is formed by starting at the leaf of the tree and following a path to the root.

The global database name for mfg is created by concatenating the nodes in the tree as follows:. While several databases can share an individual name, each database must have a unique global database name.

For example, the network domains us. The global database naming system distinguishes the sales database in the americas division from the sales database in the europe division as follows:.

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. For example, if the global database name for hq is hq. You can then name the database link whatever you want.

For example, you can name a database link to hq. 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 :. Oracle Database lets you create private , public , and global database links.

These basic link types differ according to which users are allowed access to the remote database:. Note : In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated.

In this document, global database links refer to the use of net service names from the directory server. Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system.

Consider these features when making your choice:. When creating the link, you determine which user should connect to the remote database to access the data. The following table explains the differences among the categories of users involved in database links:.

Note: A connected user does not have to be the user who created the link, but is any user who is accessing the link. Connected user links have no connect string associated with them. The advantage of a connected user link is that a user referencing the link connects to the remote database as the same user, and credentials don't have to be stored in the link definition in the data dictionary.

The following statement shows how to create the private database link to a user in a remote database with a full connection string. In this example, we will create a database link to a remote Oracle Database server located in the server First, add the following entry to tnsnames. Typically, the tnsnames. The current user must be a global user with a valid account on the remote database for the link to succeed.

If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user. For example, if the database link appears inside procedure scott. However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.

If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link.