Using Oracle Dblink to access objects between databases

Oracle has its own Dblink function, which is to logically treat multiple Oracle databases as a single database, meaning that objects in another database can be manipulated in one database, for example, we create a new data database1, We need to manipulate the tables in the database database2, or we need to manipulate the tables in the database DATABASE3 on the remote machine, and we can use this powerful feature of Dblink!
1, if we want to create a global dblink, which means that whatever role can be used, then we need to determine whether the user has Dblink permissions, if not, you need to use the SYSDBA role to authorize the user:
To see if a user has Dblink permissions:
SELECT * from User_sys_privs where privilege like Upper ('%database link% ');
No, the SYSDBA authorization is used:
Grant create public database link to dbusername;

2. Use the statement to create the Dblink:
Create DATABASE link the name of the Dblink to be created
Connect to the user name to connect the database identified by the password to connect data
using ' (DESC Ription =
(Address_list =
(address = (PROTOCOL = TCP) (host = connect to database host IP address) (port = port number))
)
(connect_data =
(service_name = Connection database service name)
)
) ';
If you create a global dblink, you must use the SYSTM or SYS user to add public before database:
Create public database ....
For example, create Test_dblink in TESTDB2 to manipulate TESTDB1 databases in host 192.168.1.254:
Create DATABASE link Tset_dblink
Connect to Username identified by Password1
using ' (DESCRIPTION =
(address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 19 2.168.1.254) (PORT = 1521))
)
(Connect_data =
(service_name = testdb1)
)
) ';

3. Using Dblink:
Query the data in another database, other modifications, deletions are the same use, is another database table name @ This database creates the Dblink name:
Select xxx from table name @dblink name;
For example, we use Dblink in TESTDB2 to view data from the Tb_user table in TESTDB1:
SELECT * from [email protected]_dblink;
To view the Dblink created in the data:
Select Owner,object_name from dba_objects where object_type= ' DATABASE LINK ';
SELECT * from Dba_db_links;
Delete the corresponding Dblink:
Drop database link Dblink name;
To close the Dblink connection:
Alter session close database link ' dblink_name '
To create and delete a view:
Create or replace view name as (select field from user. Table name @dblink1);
Drop view name;

This article transferred from: http://blog.csdn.net/harderxin/article/details/39639181

Using Oracle Dblink to access objects between databases