Two effective methods for creating Oracle dblink

Two effective methods for creating Oracle dblink

Two different database servers read data from one user on one database server from another database server. In this case, you can use dblink.

In fact, dblink is similar to the view in the database. When creating dblink, you need to know the IP address, SSID, and database username and password of the database to be read.

You can create an instance in either of the following ways:

1. Local services have been configured
Create public database link fwq12 connect to fzept identified by Neu using 'fjept ';

Create database link database connection name connect to user name identified by password using 'instance name of locally configured Data ';

2. Local service not configured

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 )))';

Host = IP address of the database, Wan: SERVICE_NAME = SSID of the database, lan SID = SSID of the database.

In fact, the two methods are similar in dblink configuration. I personally feel that the second method is better, so it is not affected by the local service.

You can use net8 easy config or modify tnsnames. ora to define the database connection string.

When the database parameter global_name = true, the database connection name must be the same as the remote database name.

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

Select * From global_name;

Query tables in a remote database:

Select ...... From table name @ Database Link name;

Querying, deleting, and inserting data are the same as operating a local database, except that the table name must be written as "table name @ dblink server.

Additionally, create a synonym:

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 to add public to the database.

References: