Analysis of Oracle Database Links Implementation Method

What is Database Links? First, we will explain its role: to allow users to access another remote database through one database. The Database Link stores the connection information of the remote Database.

As shown in:

Scott can query the emp table of the remote database in the local database. We can create an alias (Synonym) emp on our local database, then we can directly query the emp table on the remote database through select * from emp, without querying through the select * from emp@hq.acme.com.

This remote database can be any remote database supported by Oracle: Oracle, DB2, SQL Server, Taradata, MySQL... And so on. Later, I will introduce how to connect to databases of non-Oracle products.

Now that we know what Database Link is, how it is used, and the nature of Database Link, we will introduce the links of Database Link in several connection modes:

Link Type	Description
Connected user link	You can check the name of this type to determine that the account and password of the currently connected user are used to access the remote database. Therefore, you need to have a username and password that are the same as the local account on the remote database.
Fixed user link	This type means that the specified account and password are used to access the remote database. Therefore, you only need to assign the account the permission to access the remote database.
Current user link	When you need to connect a user to a local database, it is connected by a global user identity. In this way, the global user can use the context of the specified account, without storing the password information in the link.

The above is used to tell Database Link what identity is used for connection.

Database Link has the following types:

Type	Description
Private	Database Lin can only be used by the Database Link Owner.
Public	All users in this Database can use this Database Link.
Global	Users in databases on the network can use this database link. when Oracle uses the directory server, it creates the global database link in all the databases in the directory and controls it in a unified manner. Therefore, all users in the database under the directory can use it.

Okay. Let's see how to create a Database Link:

Syntax: create [type] database link

Statement	Description	Access Method Type	Connection Type
Create database link dblinkName using 'nsame ';	Create a database link by using the network service name. The Database Link name is dbLinkName. The network service name is NSName.	Connected user link	Private
Create public database link dblinkName connect to CURRENT_USER using 'nsame ';	Create a database link by using the network service name. The Database Link name is dbLinkName. The network service name is NSName.	Current user link	Public
Create database link dblinkName connect to username identified by password using 'nsame ';	Create a database link by using the network service name. The Database Link name is dbLinkName. The network service name is NSName. the user account username and password are used to access the remote Database.	Fixed user link	Private

The above NSName creation method needs to be configured in Oracle_home/Network/admin/tnsnames. ora. Beyond the scope of this article, I will not introduce it for the moment.

If you do not know how to configure NSName, we can write it as follows:

Create database link dblinkName dblinkName connect to username identified by password using ‘NSNName= ( Address_List= ( (Adress=(protocol=TCP)(host=RemoteServerName)(port=1521)) ) (Connect_Data=(services_Name)) )’

The protocol is the access protocol used. The RemoteServerName is the server name or IP address of the remote database, and the port is the port monitored by Oracle in the remote server (another Oracle database or Oracle Gateway can be used, oracle Gateway will be explained later ). Services_Name is the name of the service provided by the remote server. For example, if the remote server RemoteA is installed with Oracle Database, the listening port is 1521, and the Database service name is orcl, the preceding statement is rewritten as follows:

Create database link dblinkName dblinkName connect to username identified by password using ‘NSNName= ( Address_List= ( (Adress=(protocol=TCP)(host=RemoteA)(port=1521)) ) (Connect_Data=(orcl)) )’

The preceding section briefly describes how to create a Database Link in Oracle database link and how to create a Database Link.

After the creation, use the following: (if the access permission is met)

Select * from remoteTableName@dblinkName;

Here, remoteTableName is the name of the table in the remote database to be accessed, and dblinkName is the database connection we created previously. In this way, every time you access a table, it will be a little difficult to write. For common users, such interfaces may not be provided. In other words, we may only let users know what tables or views are available here. At this time, we can use the alias (synonym) to simplify/hide interfaces. The syntax is as follows:

CREATE [PUBLIC] synonym_name FOR [schema.]object_name[@database_link_name];

Synonym supports the following objects:

◆ Tables

◆ Types

◆ Views

◆ Materialized views

◆ Sequences

◆ Procedures

◆ Functions

◆ Packages

I will not explain it too much here. We will create a synonym for the database link we created earlier. The Code is as follows:

Create public tableName for remoteTableName@dblinkName;

After the creation, we can access the tables in the remote database as follows:

Select * from tableName;

The preceding statement is equivalent

Select * from remoteTableName@dblinkName;

1. Oracle Database User management and data import and export
2. Oracle performance optimization achieved through Partitioning technology
3. A simple view on Oracle concurrent processing mechanism
4. Solution to Oracle locking
5. Three methods to simplify the management of Oracle table columns