Create a database link)

Database linke is the object that establishes the path from one database to another. database link allows you to query remote tables. In my understanding, it can be regarded as a distributed database usage.

Database link is a one-way connection. Since it is an object, you can naturally query related information in the xxx_objects table. Before creating a database link, you need to clarify several things:

1. confirm that the server from which the cube is created can access the remote database.

2. You need to configure a remote database connection string in tnsnames of the cube.

3. Only connections configured on the server can be used in dblink. If connections configured only on the client cannot be used in dblink, I have made this mistake, an error is reported during execution:

Select * from t @ dblinktest
*
ERROR at line 1:
ORA-12154: TNS: cocould not resolve service name

Obviously, it is equivalent that the server does not configure tnsname, so the service name configuration of the remote database cannot be found.

Creation process:

1. Configure tnsname for the remote database on the server where the cube is created.

2. The tnsnames configuration name of the tnsping remote database.

3. Execute the following command after logging on to sqlplus:

SQL> create database link dblinktest connect to user identified by passwd using 'ceshi ';

Where:

Dblinktest is the name of database link.

User is the user name of the remote database.

Passwd is the password of the remote database.

Ceshi is the name of tnsnames configured on the database server.

4. Execute:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';
OWNER OBJECT_NAME
----------------------------------------
OPEN DBLINKTEST

You can see that the database link has been created.

5. Execute:

SQL> select * from t @ dblinktest;
ID
----------
0
2
3
4
5

Of course, you can also execute the insert statement:

SQL> insert into t @ dblinktest values (1 );
1 row created.

In addition, database links are classified into public and private. The default value is private, which is only available to the current user. If it is create public database link..., all users can use the database link with this name.