Oracle DBLink Introduction

1. Basic concepts:

Database connection strings are mainly used to establish access methods for remote databases. They can directly read or modify data in remote Oracle databases. The database connection string can be PUBLIC connection or PRIVATE connection. This is similar to synonyms.

1. Creation Syntax:

CREATE DATABASE LINK TEST CONNECT TO USERNAME IDENTIFIED BY PASSWORD

USING 'connect _ string ';

Explanation: TEST is the name of the database connection string. You can use this name to call the remote database.

USERNAME is the legal Oracle user name used to connect to the remote database. PASSWORD is the legal PASSWORD that the user uses to connect to Oracle.

CONNECT_STRING is the database connection string defined in the tnsnames. ora file on the host where the Oracle database is located.

2. Usage:

Select count (*) FROM TABLE_NAME @ DB_LINK WHERE WHERE_CLAUSE; // view the remote database content.

Update test. TEST @ DB_LINK SET SO_NBR = NEW_SO_NBR

WHERE WHERE_CLAUSE; // modify the content of the remote database.

In actual use, you can create a synonym for the remote table to further increase transparency, so that the database connection string is completely transparent to the program and developers.

Create synonym TABLE_NAME FOR TABLE_NAME @ DB_LINK;

3. Database Connection Management:

1. How do I know the current user and database connection strings in the current system?

SELECT * FROM USER_DB_LINKS;

This query displays the name, user name, password, host to be connected, and creation period of the database connection of the current user.

SELECT * FROM DBA_DB_LINKS;

This query will show the creation users, connection usernames, hosts to be connected, and creation periods of all database connections in the current system.

SELECT * from v $ DBLINK;

This query shows the currently opened database connection.

4. Common error handling:

//////////////////////////////////////// ////////////////////////

1. Service name cannot be decomposed:

Log on to the remote server:

Net97> show parameters global_name

NAME TYPE VALUE

-------------------------------------------------------------------------

Global_names boolean FALSE

Net97>

Net97> drop database link jinhua;

The database link has been dropped.

Net97> create database link jinhua

2 connect to username identified by password

3 using 'jinhua ';

The database link has been created.

Net97> select * from so @ jinhua;

Select * from so @ jinhua

*

ERROR is located in row 1st:

ORA-12154: TNS: the service name cannot be broken down.

Net97> conn username/password @ jinhua;

Connected.

Net97>

This is because the service name jinhua is not defined in the tnsnames. ora file on the Unix host where the Oracle database is located. Therefore, the service name cannot be decomposed.