Skills for establishing Oracle database links

If you want to know how to establish a connection to an Oracle database, you can click the following article to view the correct usage of the connection to an Oracle database, I have a better understanding. The following is a detailed description of the text.

Database link is a data communication LINK created to access a remote DATABASE in a distributed environment. Database links hide the complexity of remote database access. Generally, we call the database being logged on as a local database, and the other database as a remote database. With the database link, you can directly access the table of the remote database through the database link.

The common form is to access the link of a fixed user in the remote database, that is, to the specified user. The statement for creating a connection to a database in this form is as follows:

Create database link name connect to account identified by password

USING service name;

Create database link system permission is required to CREATE a database link.

Once the Oracle database connection is established and tested successfully, you can access the table of a remote user in the following form.

Table Name @ Database Link name

For example, create and use a database link on the LAN.

Step 1: Create the service name of the remote database. Assume that the name of another database service on the LAN is MYDB_REMOTE.

Step 2: log on to the SCOTT account of the local database and create a database link:

 
 

  
  
CONNECT SCOTT/TIGER@MYDB  
  
  
CREATE DATABASE LINK abc CONNECT TO scott
IDENTIFIED BY tiger USING 'MYDB_REMOTE';   
 
 

The execution result is:

The database link has been created. Query the data of a remote database:

 
 

  
  
SELECT *　FROM emp@abc; 
 
 

Result omitted.

Step 4: query a distribution:

 
 

  
  
SELECT ename,dname FROM emp@abc e,
dept d WHERE e.deptno=d.deptno; 
 
 

Result omitted.

Note: In this example, the remote Oracle database service name is MYDB_REMOTE, And the created Database Link name is abc. emp @ abc, which indicates the emp table of the remote database. Step 4 is a joint query. The data comes from the dept table of the local server and the emp table of the remote server.

If a programmer needs to search for many rows in a table, each row in the result searches for data in other tables. Finally, the programmer creates an independent UPDATE command to UPDATE the data in the first table in batches. Similar tasks can be completed in an UPDATE command by using multi-column subqueries in the set clause. When a task can be completed in a single SQL command, why stream data online? We recommend that you carefully learn how to maximize SQL functions.