Go Oracle's approach to creating DBLink

http://blog.csdn.net/davidhsing/article/details/6408770

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:

1. Grant Create public database link to dbusername;

2. Use the user login PL/SQL to use the command:

1. --The first method: Requires database B mapping in Tnsnames.ora on server A
2. ----Create database link name, connect to user name identified by password using ' instance name of locally configured data ';

Using the graphical configuration interface is as follows:

[C-sharp]View Plaincopyprint?

1. --The second method: direct configuration
2. --If you create a global dblink, you must use the SYSTM or SYS user to add public before database .
3. Create/ * public */Database link DBLINK1
4. Connect to Dbusername identified by Dbpassword
5. 　　Using ' (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521))) (Connect_data = (SERV  Ice_name = ORCL)) ';
7. --Database parameter Global_name=True requires that the database link name is the same as the remote database name. The database global name can be isolated with the following command
8. --select * from Global_name;

drop database link to_test;

Create DATABASE link To_test connect to dbzy_20140903 identified by dbzy_20140903 using ' (DESCRIPTION = (address_lis T = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.111.222) (PORT = 1521))) (Connect_data = (server=dedicate D) (service_name = orcl)) ';

3. Query data:

1. --querying, deleting, and inserting data is the same as manipulating the local database, except that the table name needs to be written as "table name @dblink Server".
2. Select xxx from table name @ database link name;

4. Delete DBLink

1. Drop/ * public */Database link dblink1;

5. Create and delete synonyms

1. Create or replace synonym synonym name for table name;
2. Create or replace synonym the synonym name for the user. Table name;
3. Create or replace synonym synonym name for table name @ database link name;
4. drop synonym synonym name;

6. Create and delete views

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

7. Note:

Creating a DBLink is simple, but in the background of the use of the lock, the way to view the lock can go to the console to see or query the database. Each time you use the Dblink query, you will create a connection with the remote database, Dblink should not automatically release this connection, if it is a large number of dblink queries, the number of connections to the Web project will be insufficient, resulting in the system does not function properly, causing the system to run out of normal operation.

Reference article:

1, http://www.cnblogs.com/sishierfei/archive/2010/12/20/1911606.html

2, http://www.51testing.com/?uid-194762-action-viewspace-itemid-90513

3, http://hi.baidu.com/jjpx/blog/item/a583742451884f124c088dbe.html

Go Oracle's approach to creating DBLink