Oracle to create Dblink method __oracle

1, if you need to create a global dblink, you need to first determine the user has the right to create Dblink:

SELECT * from User_sys_privs where privilege like Upper ('%database link% ');

If not, you need to use the SYSDBA role to empower the user:

Grant create public database link to dbusername;

2, use the user login pl/sql, use the command:

--The first method: Require database B mapping in the Tnsnames.ora on database Server A
----Create DB link database links name connect to user name identified by password using ' Locally configured data ' instance name ';

The graphical configuration interface is as follows:

--The second method: direct configuration
-If you create a global dblink, you must use the SYSTM or SYS user to add public before the database.
Create/* Public */Database link DBLINK1
Connect to Dbusername identified by Dbpassword
Using ' (DESCRIPTION = (Address_list = (address = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521)) (Connect_data = (service_ NAME = ORCL))] ';

--Database parameter global_name=true requires the database link name to be the same as the remote database name. The database global name can be identified with the following command
--select * from Global_name;

3, query data:

Querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as a "table name @dblink server."
Select xxx from table name @ database link name;

4, delete Dblink

Drop/* Public */Database link dblink1;

5. Create and delete synonyms

Create or replace synonym synonym name for table name;
Create or replace synonym synonym name for user. Table name;
Create or replace synonym synonym name for table name @ database link name;
drop synonym synonym name;

6. Create and delete views

Create or Replace view view name as (select field from user. Table name @dblink1);
Drop view view name;

7. Note:

Creating a dblink is simple, but there is a lock in the background in use, and the way to view the lock is to see or query the database in the console. Every time you use the Dblink query, you will create a connection with the remote database, Dblink should not automatically release the connection, if it is a large number of Use Dblink query, will cause the number of Web project connections are not enough, resulting in the system can not run normally, resulting in the system does not run properly.

This article comes from the Linux commune website (www.linuxidc.com) original link: http://www.linuxidc.com/Linux/2011-07/39614.htm