Create databaselink in Oracle

Multiple ORACLE databases physically stored in the network can be viewed as a single large database logically. Users can access the data in the remote database through the network.

Multiple ORACLE databases physically stored in the network can be viewed as a single large database logically. Users can access the data in the remote database through the network.

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, the collaborative processing between servers is completely transparent to workstation users and applications, developers do not need to care about the network connection details, the specific distribution of data on network nodes, and the coordination process between servers.
The links between databases are established on 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:

SELECT * FROM GLOBAL_NAME;

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

Alter system set GLOBAL_NAME = TRUE/FALSE;

<1> when the database parameter global_name is set to false, the Database Link name is not required to be the same as that of the remote database.

Connect and communicate between oracle databases.
The syntax for creating a database link is as follows:
CREATE [PUBLIC] database link link

Connect to username identified by password

USING 'string string'

Where:
-- Demona is the connection string created with net8 easy config.
Set global_names In the init. ora file of the target database to false.
Restart database settings to take effect
Or run the command as sys.

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 create 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 zrhs_link

Using 'zrhs ';

If the user name and password are not specified, ORACLE uses the current user name and password to log on to the remote database.

USING is followed by a link string, that is, the network service name of the remote database. This service name is stored in TNSNAMES. the ORA file defines the protocol, host name, port, and database name.

The statement for deleting a database link is:

DROP [PUBLIC] database link zrhs_link

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 worker @ zrhs_link;

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

Create synonym worker_syn FOR worker @ zrhs_link;

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

Create view worker as select * FROM worker @ zrhs_link where... ;

Now this view can be treated with any other view in the local database, or authorized to 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 camel. worker @ zrhs_link;

<2> when the database parameter global_name = true, the database connection name must be the same as the remote database name.
You can use the following command to check the global name of the database:

SELECT * FROM GLOBAL_NAME;

Atabase link (dblink) -- Database link
Syntax for creating dblink:
SQL> create database link name
Connect to username identified by password
Using 'host string name ';
For example:
SQL> create database link ora9i.us.oracle.com ### here, us.oracle.com is the default domain name of oracle ###
Connect to scott identified by tiger
Using 'sun ';

1) The dblink name must be the same as the global database name (global_name) of the remote database;
2) User name. The password is the remote database user name and password;
3) The host string is the string defined in tnsnames. ora;
4) dblink cannot be created between two databases with the same name;
Then, you can access the remote database through dblink.
For example:
SQL> select * from table name @ ora9i.us.oracle.com;
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 ';

Example

Oracle Database Connection is the same as setting up a connection to the database in the program.

If the database is not on the local host, it must be in $ ORACLE_HOME/network/admin/tnsnames. configure the corresponding tns in ora, and then the program can access the database through the configured tns. However, if java accesses oracle through the thin method, you can use the locally configured tns alias, you can also use the tns full resolution name and the full descriptor after the alias equals sign; as follows:

TESTCZ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.70.9.12) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTCZ)
)
)

For example.

There are now two databases

Adb. the username and password are adb and adb respectively. The tns name configured on the local host is tns_a and the host;

Bdb, And the username and password are bdb/bdb respectively. The tns name configured on the local host is tns_ B and the host B is located;

Now we need to create a dblink on the adb to connect to the bdb database;

Method 1:

Edit the tnsnames. ora file on host a to configure the tns alias tns_ B OF THE bdb database, as shown below:

Tns_ B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.70.9.12) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dbtestb)
)
)

Create a database connection as follows:

Create database link

Connect to bdb identified by bdb

Using 'tns _ B ';

Method 2:

If you do not have the permission to modify tnsnames. ora, you cannot create a tns alias for the adb database. Therefore, you can only use a full-write parsing symbol when creating the dblink. The method for creating a dblink is as follows:

Create database link

Connect to bdb identified by bdb

Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.70.9.12) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dbtestb)
)
)';

After creating a tns alias, you can use sqlplus username/password @ tnsname to test whether the created tns alias is correct.

A dblink example created in the production system:

Create database link NEW_DBLINK
Connect to AIIPS identified by "1qaz2wsx"
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.70.193.12) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = zjpub)
)
)';