Oracle Database Link

The establishment and use of Oracle database link

First, the establishment of the database link:


1, the database link building syntax is generally: Create databases Link[db_link_name] CONNECT to

[USER_NAME] identified by [password] USING ' [tns_name] ';

which

[Db_link_name]: is the service name of the database to which you want to connect, that is, the real name of the database (usually the SID).

[Usre_name]: is the name of the user you want to connect to.

[PASSWORD]: Is the password of the user you want to connect to.

[Tns_name]: is the service name of the database to which you want to connect, that is, the NET8 service established on this machine is named.

2, when using the current user as a connector, the syntax can be: CREATE DATABASE link[db_link_name] Connect to Current_User using ' [Tns_name] ' (In some cases, the database link established by this method is not active and is not known to be syntactically incorrect, and is not recommended for use at this stage.)

3, when using connected users as the connector, the syntax can be: CREATE DATABASE Link[db_link_name]

USING ' [Tns_name] '.

4, to establish a common database link syntax can be: As long as you add the Public keyword after create.

5, if you in the Init.ora file Global_names = True can only use the method above, if global_names =false,[db_link_name] not necessarily the real name of the database, can be a random name. That is, when global_names = True, each user of the database can have only one database link, and when global_names = False, each user may establish n multiple links, but generally it is meaningless.



Second, the use of database links:

1, the main purpose of the database link is to achieve access to the remote database, you can directly through the database link to the remote database

Table to read and write, you can also set up a remote database view and consent word locally, and also use the database link for a dual-machine hot backup.

2. Direct access: SELECT * From[user_name]. [Table_name]@[db_link_name];insert,update,delete, etc. also apply.]

3, establish synonyms: Create Synonym[user_name]. [Synonym_name] for [user_name]. [table_name]@

[Db_link_name]; View is also a reason.

4, because the dual-machine hot preparation is another complex problem, this article does not repeat. If you want to achieve a few table synchronization between the two databases, do a dual machine is too complex, you can consider using a trigger to solve, in the trigger with the above method to reference the remote table, insert it, delete can also be synchronized.



Links between databases are based on database link. To create a DB link, you must first set the link string on each database server.

1, the link string is the service name, first configure a service name locally, the address points to the remote database address, the service name for the future you want to use the database chain name: tobeijing

2. Create a database link,

Go to the system administrator sql> operator and run the command:

Sql>create Public Database link Beijing Connect to Scott identified Bytiger using ' tobeijing ';

Created a link to Scott users and the Beijing database Beijing, we can query Scott data in Beijing:

Sql>select * FROM [email protected];

In this way, the data of Shenzhen and Beijing Scott users can be processed as a whole.



3, establish synonyms, in order to make the distributed operation more transparent, Oracle database has synonyms object synonym

Sql>create synonym bjscottemp for [email protected];

You can then use Bjscottemp to replace the Distributed link operation with the @ symbol [email protected].



4, view all the database links, enter the system administrator sql> operator, run the command:

Sql>select owner,object_name from Dba_objects whereobject_type= ' DATABASE LINK ';


If you use a data connection error ORA-02085

sql> SHOW PARAMETER Global_names

Global_names Boolean

TRUE

sql> ALTER SYSTEM SET global_names=false;


Data connections make it easy to reference data from other databases, but you may encounter ORA-02085 errors if you set them incorrectly. If the Global_names parameter of the connected party is set to True, then the database connection is required to have the same name as the other instance name. This can be solved by either of the following 3 methods:

1. Modify the Global_names parameter to False
2. Set the Global_name to the same name as the database connection

3. Delete the database connection and recreate the same database connection as the other instance name. Here are the test results:

sql> CREATE public DATABASE LINK "master.com" USING ' ora9i ';

The database link was created.



SELECT * from v$parameter where name = ' Global_names ';

sql> ALTER SYSTEM SET global_names=false;



or modify Global_names = False in the D:\Oracle\admin\einfo\pfile\init.ora file to restart the database.

Sql> SELECT * FROM [email protected];

X

sql> ALTER SYSTEM SET global_names=true;

The system has changed.

Sql> SELECT * FROM [email protected];

SELECT * FROM [email protected]

ERROR on line 1th:

ORA-02085: Database link master.com and ora9i connection

sql> ALTER DATABASE RENAME global_name to master.com;

The database has changed.

Sql> SELECT * FROM [email protected];


CreateUser Guest_b identified by GUEST_B_PSW default tablespace table_space temporarytablespace pcs_temp;

Grantconnect to Guest_b;

A use an administrator account to authorize two tables for guest_b users, and the following grants full control over the permissions of both tables.

The code is as follows:

Grantall on TABLE_A1 to Guest_b;

Grantall on TABLE_A2 to Guest_b;

A in Netmanager to the netservicename of the B database server, you can also modify the files in the Oracle installation directory \oracle\network\admin\ Tnsnames.ora, add a netservicename, such as "To_b".

Create a data link named "Goto_b" to use the Netservicename:to_b created in step fourth. Here it is assumed that B likewise created an account guest_a/guest_a to provide for a use.

CREATE public DATABASE LINK "Goto_b"

CONNECT to ' guest_a ' identified by "Guest_a"

USING ' To_b ';

An example is provided, which can be created directly without configuring Netservicename:



--drop Existing Database link

Dropdatabase link toccic;

--create Database Link

CreateDatabase Link Toccic

Connect to Ztry identified by Ztry

Using ' (DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST =fengwenwei) (PORT = 1521))

)

(Connect_data =

(SID = ztry)

)

)‘;



Summary

1. When creating a link, use the Enterprise Manager console to create a test connection if it is not successful using the build link code.

2. Pay attention to the use of the account on both sides, do not confuse.

3. Note: service_name in Oracle can correspond to multiple SIDs.

4. Note If the link is not established successfully, verify that the parameters in Oracle are configured correctly, such as: Global_names needs to be set to true. Oracle9i defaults to False when installing.

Oracle Database Link