How to Create a database link in Oracle

Multiple Oracle databases physically stored in the network can be logically considered as a single large database. Users can access the data in the remote database through the network, while the collaborative processing between servers

It is completely transparent to workstation users and applications. Developers do not need to care about the details of network links, the specific distribution of data on network nodes, and the coordination process between servers. Links between databases

Connect to the database link. To create a database link, you must first set the LINK string on the DATABASE that creates the LINK, that is, configure the local network service name of a remote DATABASE.

You can run the following command to check the Global Database Name:

SQL> SELECT * FROM GLOBAL_NAME;

You can use the following statement to modify the parameter value:

SQL> ALTER SYSTEM SET GLOBAL_NAME = TRUE/FALSE;

Connection and communication between oracle databases:

The syntax for creating a database link is as follows:

CREATE [PUBLIC] database link link_name

Connect to username identified by password

USING 'zytydic'

Where:

Zytydic is the link string defined in the local tnsname. ora. The content is as follows:

Zytydic =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = orcl)

)

)

Note: The account used to CREATE the database link must have the system permission of create database link or create public database link. The account used to log on to the remote DATABASE must have the create session permission.

Both permissions are included in the CONNECT role (create public database link permission is included in DBA ). A public database link is available to all users in the database, while a private link is only

Available to users who have created it. It is impossible for a user to authorize a private database link to another user. A database link is either public or private.

When creating a database link, you can also use the default logon mode, that is, do not specify the remote database user name and password:

Create public database link test_default_type_dblink

Using 'zytydic ';

Without specifying the user name and password, ORACLE uses the current user name and password to log on to the remote database. If you use www.bkjia.com to locally log on to the local database db_A as an emcd user, the created database

Link test_default_type_dblink is created based on the local user account information. When you want to use this DB Link to view the data information of the remote database db_ B, the premise must be that db_ B also has

The same account exists, that is, db_ B also has an account emcd, And the password is the same as the password of emcd on db_A, otherwise this DB Link is unavailable. USING is followed by a link string, that is, remote

The Network Service name of the database. The service name is saved in the TNSNAMES. ORA file, which defines the protocol, host name, port, and database name.

If you have two database servers, db_A and db_ B, and user_a under db_A needs to use database link to access the data of user_ B under db_ B, follow these steps:

(A). First, the user user_ B must have the select permission.

(B). Create a connection string in tnsnames. ora on db_A, that is, zytydic above. You can connect to db_ B from db_A.

(C). Create a db link after logging on to db_A. The format is as follows:

Create database link link_A_to_ B _name connect to user_ B identified by user_ B's password using 'tnsname _ atob ';

Tnsname_AtoB is the connection string to the remote host in tnsname. ora.

The actual DB Link SQL statement created on my own machine is as follows:

Create database link taowei_to_101 connect to ga_bzk identified by ga_bzk using 'zytydic ';

(D). Test whether data can be read.

Select * from dual @ link_AtoB_name: dummy x indicates that the setting is successful.

At this time, you can view the data on the remote host through taowei_to_101:

Select * from tbl_bjflb @ taowei_to_101;

The statement for deleting a database link is:

DROP [PUBLIC] database link taowei_to_101

Database link reference

Generally, when you reference a database link, you can directly put it behind the called table name or view name, and use a @ in the middle as the delimiter:

SELECT * FROM tbl_bjflb @ taowei_to_101;

For frequently used database links, you can create a local synonym for ease of use:

Create synonym tbl_bjflb _ syn FOR tbl_bjflb @ taowei_to_101;

You can also create a local remote view for ease of use:

Create view tbl_bjflb as select * FROM tbl_bjflb @ taowei_to_101 where... ;

Now this view can be treated like any other view in the local database. www.bkjia.com can also authorize other users to access this view, but this user must have the permission to access the database link.

In another case, the table to be accessed is not under the remote account specified in the database link, but the account has the permission to access the table, add the table username before the table name:

SELECT * FROM emcd. emcd_data_source @ taowei_to_101;

You can also create snapshots to automatically transmit remote data through dblink.

View All database links, go to the system administrator SQL> operator, and run the following command:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';