How to deploy Oracle database link correctly

The following articles mainly introduce the actual operation steps of the distributed Oracle database link and the description of what I should pay attention to during actual operations, if you are interested in the actual operations, you can click to view the following articles.

Access Method:

 
 

  
  
select * from MyTableSpace.MyTable@MyDB 
 
 

1. Create a private connection

 
 

  
  
Create database link fmydb connect to lpuser identified by lpuser1 using 'lp10. 119.40.7'
  
  
Drop database link fmydb [Note that public is not available here]
 
 

2. Create a public connection

 
 

  
  
Create public database link fmydb connect to sys identified by sys using 'lp10. 119.40.7'
  
  
Drop public database link fmydb [Note that public is required here]
 
 

The difference between public and public is that if public is available, all oracle users on this machine can query remote databases, if no public account is available, only the user who creates the connection on the machine has the right to query the remote database.

If you create a global dblink, you must use either mongom or sys.

I. Two creation methods

First:

1. Use Net Manager to set up remote services and listeners based on remote information.

2.

 
 

  
  
create public database link fwq12  
  
  
connect to fzept identified by neu  
 
 

Using 'fjept '// fjept indicates the service name Just configured.

Second:

Directly create a link:

 
 

  
  
create database link linkfwq  
  
  
connect to fzept identified by neu  
  
  
using '(DESCRIPTION =  
  
  
(ADDRESS_LIST =  
  
  
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.142.202.12)(PORT = 1521))  
  
  
)  
  
  
(CONNECT_DATA =  
  
  
(SERVICE_NAME = fjept)  
  
  
)  
  
  
)';  
 
 

Create synonym name FOR table name;

Create synonym name FOR table name @ Database Link name;

Delete dblink: drop public database link linkfwq.

If you create a global dblink, you must use the mongom or sys user and add public before the database.

 
 

  
  
SQL>create synonym bjscottemp for emp@beijing; 
 
 

Therefore, you can use bjscottemp to replace the Distributed Link operation with the @ symbol emp @ beijing.

ORA-02019: Description of remote database connection not found

1. >>> create a DATABASE LINK

 
 

  
  
CREATE PUBLIC DATABASE LINK ABC  
  
  
CONNECT TO SA IDENTIFIED BY PASSWORD  
  
  
USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.100)
(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = AAA)))';  
 
 

2. >>> Test

 
 

  
  
select * from test@ABC 
 
 

3. >>> note the settings of GLOBAL_NAME in INIT. ORA when using DBLINK,

If it is true, use the full name of DBLINK, which can be found in all_db_links. If it is FALSE, do not use the full name.

Use the full name, such:

 
 

  
  
select * from test@ABC.US.ORACLE.COM 
 
 

4.

 
 

  
  
SQL> select * from global_name;  
  
  
GLOBAL_NAME  
  
  
LOCALSR  
 
 

5. >>>> update global_name set GLOBAL_NAME = 'localsr. US. ORACLE. com' where GLOBAL_NAME = 'localsr'

6. >>> restart routine> OK

 
 

  
  
SQL> desc global_name;  
  
  
Name Type Nullable Default Comments  
  
  
GLOBAL_NAME VARCHAR2(4000) Y global database name   
 
 

The above content is the description of Oracle database link, hoping to help you in this regard.