Create tables for oracle dblink & SQL operations on different databases

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

 

The following is a reference clip: 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

　　

The following is a reference clip: 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, service_name = 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 for database connection strings or directly modify the definition in TNSNAMES. ORA.

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

You can use the following command to check the global name of the database:

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.

 

In addition:

 

SQL statements enable cross-SQL server database operation instances-query Remote SQL, local SQL database and remote SQL data transmission
(1) query the data of test1 in the database (TT) Table of 192.168.1.1
Select * from opendatasource ('sqloledb', 'server = 192.168.1.1; uid = sa; pwd = 123456; database = tt'). TT. dbo. test1

(2) Insert the table test1 of the 192.168.1.1 database (TT) from Table test2 of the database 192.168.1.2
Insert into opendatasource ('sqloledb', 'server = 192.168.1.1; uid = sa; pwd = 123456; database = tt '). TT. dbo. test1 (id, [name], password) select id, [name], password
From opendatasource ('sqlodb', 'server = 192.168.1.2; uid = sa; pwd = 123456; database = tt'). TT. dbo. test2