Database Link Summary

Many Oracle databases that are physically stored on the network can logically be viewed as a single large database, where users can access data from remote databases, and the collaborative processing between servers is completely transparent to workstation users and applications, and developers do not need to care about the link details of the network, The specific distribution of data in network nodes and the coordination work process between servers.
Links between databases are built on database link. To create a database link, you must first set up the link string on the linked databases, which is to configure the local network service name for a remote database.
Configuration of link strings see the "Client Connection Server" section.
The database global name can be identified by the following command:
SELECT * from Global_name;
Modify the parameter values by using the following statement:
ALTER SYSTEM SET Global_name=true/false;

<1>, when the database parameter is global_name=false, the database link name is not required to be the same as the remote database name. Connect communication between Oracle databases. The syntax for creating a database link is as follows:
CREATE [public] the DATABASE link link CONNECT to username identified by password USING ' connectstring ' Where: Global_names is set to FAL Se Restart Database settings to take effect or execute with SYS user
Note: The account that creates the database link must have system permissions for Create or create public database link, and the account used to log on to the remote databases must have the Create session permission. Both of these permissions are included in the CONNECT role (CREATE public DATABASE LINK permissions are in the DBA).

A public database link is available to all users in the database, and a private link is available only to the user who created it. It is not possible for a user to authorize a private database link to another user, a database link is either public or private.
When you create a database link, you can also use the default logon method, which does not specify a username and password for the remote database: Create public database link Zrhs_link using ' zrhs '; Without specifying a username and password, ORACLE logs on to the remote database using the current username and password. The USING following specifies the link string, the network service name for the remote database, which is saved in the Tnsnames.ora file, where the protocol, host name, port, and database name are defined.
(if there are two database servers db_a and Db_b, db_a users user_a need to use database link to access the data db_b under User_b, there are the following steps:
1, first user user_b, this user must have SELECT permission.
2, in the Db_b on the Tnsnames.ora need to establish a connection string tnsname_btoa, you can connect to db_a from Db_b.
3, in the db_a on the Tnsnames.ora need to establish a connection string Tnsname_atob, you can connect to Db_b from Db_a.
4, and then on the landing on the db_a to establish a DB link, the format is as follows: Create DATABASE link Link_atob_name connect to user_b identified by User_b using ' Tnsname_atob '; Link_atob_name must be the global name (SID) of the remote database, Tnsname_atob is the connection string to the remote.
5, test whether can read data. The SELECT * from Dual@link_atob_name result is: DUMMY X indicates that the setting was successful. ）
The statement that deletes the database link is: DROP [public] database link Zrhs_link reference
Typically referencing a database link, you can put it directly behind the called Table name or view name.
The middle uses a @ as a separator: SELECT * from Worker@zrhs_link; For frequently used database links, you can establish a local synonym, easy to use: Create synonym Worker_syn for worker@zrhs_link; You can also create a local remote view for easy use: Create VIEW worker as SELECT * from Worker@zrhs_link where ...; This view can now be treated like any other view in the local database, or you can authorize other users to access this view, but the user must have permission to access the database link.
In the other case, the table you want to access is not under the remote account specified in the database link, but the account has permission to access the table, then we add the user name of the table before the table name:
SELECT * from Camel.worker@zrhs_link;
<2>, when the database parameter is global_name=true, it requires the database link name to be the same as the remote database name the global name of the database can be detected with the following command of the SELECT * from Global_name;
Database Link (dblink)--The syntax for creating a dblink on the link of databases:
Sql>create DB Link Database link name connect to user name identified by password using ' host string name ';
such as: sql>create database link ora9i.us.oracle.com ### here us.oracle.com for the Oracle Default domain name ### connect to Scott identified by Ti Ger using ' sun ';
1) The Dblink name must be the same as the global database name (global_name) of the remote database;
2 user name, password for remote database username, password;
3 The host string is a string defined in the native Tnsnames.ora;
4 A DB link cannot be established between two databases with the same name;
Then, you can access the remote database via Dblink. Such as:
Sql>select * from table name @ora9i.us.oracle.com; You can also create snapshots (snapshot) to automate remote data transfer via Dblink. To view all the database links, go to the system administrator sql> operator, and run the command:
Sql>select owner,object_name from dba_objects where object_type= ' DATABASE LINK ';