How to Create a database link for an Oracle DATABASE?

OracleHow to Create a databaseDATABASE LINKWhat about it? In this article, we will use an instance to introduce the creation process. Next, let's take a look at this process.

Multiple ORACLE databases physically stored in the network can be logically considered as a single large database. Users can access the data in the remote database through the network, the collaborative processing between servers is completely transparent to workstation users and applications, developers do not need to care about the network connection details, the specific distribution of data on network nodes, and the coordination process between servers.

The links between databases are established on the database link. To create a database link, you must first set the LINK string on the DATABASE that creates the LINK, that is, configure the local network service name of a remote DATABASE.

You can run the following command to check the Global Database Name:

SQL> SELECT * FROM GLOBAL_NAME;

You can use the following statement to modify the parameter value:

SQL> ALTER SYSTEM SET GLOBAL_NAME = TRUE/FALSE;

Connection and communication between oracle databases:

The syntax for creating a database link is as follows:

 
 

  
  
CREATE [PUBLIC] DATABASE LINK link_name  
  
  
 
  
  
CONNECT TO username IDENTIFIED BY password  
  
  
 
  
  
USING 'zytydic' 
 
 

Where:

Zytydic is the link string defined in the local tnsname. ora. The content is as follows:

 
 

  
  
zytydic =  
  
  
 
  
  
(DESCRIPTION =  
  
  
 
  
  
(ADDRESS_LIST =  
  
  
 
  
  
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))  
  
  
 
  
  
)  
  
  
 
  
  
(CONNECT_DATA =  
  
  
 
  
  
(SID = orcl)  
  
  
 
  
  
)  
  
  
 
  
  
) 
 
 

Note: The account used to CREATE the database link must have the system permission of create database link or create public database link. The account used to log on to the remote DATABASE must have the create session permission.

Both permissions are included in the create public database link permission of the CONNECT role in the DBA ). A public database link is available to all users in the database, while a private link is only available to users who create it. It is impossible for a user to authorize a private database link to another user. A database link is either public or private.

When creating a database link, you can also use the default logon mode, that is, do not specify the remote database user name and password:

 
 

  
  
create public database link test_default_type_dblink  
  
  
 
  
  
using 'zytydic'; 
 
 

Without specifying the user name and password, ORACLE uses the current user name and password to log on to the remote database. If you use the local database db_A logged on by the emcd user, the created DB Link test_default_type_dblink is created based on the local user account information. When you want to use this DB Link to view the data information of the remote database db_ B, the premise must be that db_ B has the same account, that is, db_ B also has the account emcd, And the password is consistent with the emcd password on db_A, otherwise this DB Link is unavailable.

USING is followed by a link string, that is, the network service name of the remote database. This service name is stored in TNSNAMES. the ORA file defines the protocol, host name, port, and database name.

If you have two database servers, db_A and db_ B, and user_a under db_A needs to use database link to access the data of user_ B under db_ B, follow these steps:

(A). First, the user user_ B must have the select permission.

(B). Create a connection string in tnsnames. ora on db_A, that is, zytydic above. You can connect to db_ B from db_A.

(C). Create a db link after logging on to db_A. The format is as follows:

 
 

  
  
Create database link link_A_to_ B _name
  
  
 
  
  
Connect to user_ B identified by user_ B's password using 'tnsname _ atob ';
 
 

Tnsname_AtoB is the connection string to the remote host in tnsname. ora.

The actual DB Link SQL statement created on my own machine is as follows:

 
 

  
  
create database link taowei_to_101   
  
  
 
  
  
connect to ga_bzk identified by ga_bzk using 'zytydic'; 
 
 

(D). Test whether data can be read.

Select * from dual @ link_AtoB_name: dummy x indicates that the setting is successful.

At this time, you can view the data on the remote host through taowei_to_101:

Select * from tbl_bjflb @ taowei_to_101;

The statement for deleting a database link is:

DROP [PUBLIC] database link taowei_to_101

Database link reference

Generally, when you reference a database link, you can directly put it behind the called table name or view name, and use a @ in the middle as the delimiter:

SELECT * FROM tbl_bjflb @ taowei_to_101;

For frequently used database links, you can create a local synonym for ease of use:

Create synonym tbl_bjflb _ syn FOR tbl_bjflb @ taowei_to_101;

You can also create a local remote view for ease of use:

Create view tbl_bjflb as select * FROM tbl_bjflb @ taowei_to_101 where... ;

Now this view can be treated like any other view in the local database, and can be authorized to other users to access this view, but this user must have the permission to access the database link.

In another case, the table to be accessed is not under the remote account specified in the database link, but the account has the permission to access the table, add the table username before the table name:

SELECT * FROM emcd. emcd_data_source @ taowei_to_101;

You can also create snapshots to automatically transmit remote data through dblink.

View All database links, go to the system administrator SQL> operator, and run the following command:

 
 

  
  
SQL>select owner,object_name from dba_objects   
  
  
 
  
  
where object_type='DATABASE LINK'; 
 
 

This article introduces how to create a database link for Oracle databases. I hope this introduction will help you gain some benefits!