Oracle DB links

We know that different schemas on the same database server can search for a table or other objects, as long as the permissions are granted to the corresponding schema. But how can we read data between different database servers?

This requires dB link. There are two ways to create dB link.

1. Local services have been configured:

1. Create a database link like creating other database objects.

Create database link linknametest
Connect to userarwen identified by pswarwenusing 'tnsnamearwen ';

 

Meanings of parameters:

Linknametest: name of the DB link, which is the same as the name of the database table.

Userarwen: username of the database to be connected

Pswarwen: password of the database to be connected.

'Tnsnamearwen': there is a local file named tnsnames. ora. The content in the configured TNS name. tnsnames. ora is as follows:

Tnsnamearwen =
(Description =
(Address = (Protocol = TCP) (host = 10.30.40.50) (Port = 1521 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = Arwen)
)
)

Note: The host in it can only use an IP address, but cannot use a machine name. It will not be a problem to connect to the database in other cases, but I don't know why there is a problem in dB link.

2. Use it after creation:

For example, you need to find the table EMP in the connected database. (Note: if the table has a long field such as blob, an error is returned when you use dB link for query. I don't know why)

Select * from EMP @ linknametest where empno = 7788;

The usage is similar to that of common queries. You only need to add the database connection name after the table name.

The same applies to other objects, such as views, functions, and stored procedures. You only need to add a @ suffix.

 

2. The local service is not configured.

In fact, just like the first method, you can replace the TNS name after using with the content in the tnsnames. ora file.

Create database link linknametest
Connect to userarwen identified by pswarwenusing

'(Description =
(Address = (Protocol = TCP) (host = 10.30.40.50) (Port = 1521 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = Arwen)
)
)';

 

Supplement: No matter which connection method, if you want to create a global link dB, all the schemas created can be used.

You must use the system or sys user to log in. Then create the DB link as above. You only need to add public before the database.

Example: Create public database link linknametestconnect touserarwen
Identified by pswarwen
Using 'tnsnamearwen ';