Oracle synonyms and database 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 username 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, 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.

It is very easy for Oracle dblink to solve the problem of lock creation in the background, but there is a lock in the background. You can view the lock on the console to see or query the database. In fact, this lock is not the most disturbing. Instead, every time you use dblink for query, it creates a connection with the remote database. dblink should not automatically release the connection, if you use a large number of dblink queries, the number of connections of web projects is insufficient, and the system cannot run properly.