Oracle Database Links parsing

What is database links? Let's start by explaining what it does: enabling users to access another remote database from one database. Then database link is the connection information that stores the remote database.

As shown in the following:

The user Scott can query the local database for the EMP table of the remote database, we can create an alias (synonym) emp on our local database, and then we can query the EMP table on the remote database directly through the SELECT * from EMP. Without the need to search by select * from [email protected].

This remote database can be any Oracle supported remote database: Oracle, DB2, SQL Server, Taradata, MySQL ... Wait a minute. I'll also explain how to connect to a non-Oracle product database later.

Well, know what the database link is, what to do with, the nature of the database link, and then we introduce the database link has what kinds of link:

LINK Type

Description

Connected User Link

By looking at this type of name, you can tell that the current connected user's account and password are used to access the remote database. So this type requires a remote database with the same username and password as the local account.

Fixed User Link

This type is said to use the specified account and password to access the remote database. So only the specified account can have access to the remote database.

Current User link

Users who need to connect to the local database are connected to the identity of the global user. This allows the global user to use the context of the specified account without having to store the password information in the link.

Above is used to tell database link, we use what identity to connect.

Database Link has the following types:

Type

Description

Private

The type of database Lin can only be used by the owner of this database link.

Public

The database Link can be used by all users in this

Global

This database link can be used by users in databases on the network. Because when Oracle uses a directory server, it creates the global database link in all the databases in that directory and controls it uniformly. Therefore, all users in the database under the directory can be used.

OK, let's see how to create a database Link:

Syntax: Create [Type] Database link

Statement

Description

Access mode type

Connection type

Create database link dblinkname using ' nsname ';

Create a database link by using the Network service name. Where database link is named Dblinkname. The name of the network service is nsname.

Connected User Link

Private

Create Public Database link Dblinkname connect to current_user using ' nsname ';

Create a database link by using the Network service name. Where database link is named Dblinkname. The name of the network service is nsname.

Current User link

Public

Create Database link dblinkname dblinkname connect to username identified by password using ' nsname ';

Create a database link by using the Network service name. Where database link is named Dblinkname. The name of the network service is nsname. Access to the remote database through the user account username and its password password.

Fixed User Link

Private

The above ground nsname is created in a way that needs to be configured in Oracle_home/network/admin/tnsnames.ora. Beyond the scope of this article, I do not introduce.

If you do not know how to configure Nsname, then we can write it in the following way:

Create Database link dblinkname dblinkname connect to username identified by password using ' Nsnname=

(

address_list=

(

(Adress= (PROTOCOL=TCP) (Host=remoteservername) (port=1521))

)

(Connect_data= (Services_name))

)’

Where protocol is the access protocol used, RemoteServerName is the server name or IP of the remote database, and port is the one that Oracle monitors on the remote server (you can make another Oracle database or Oracle Gateway, About Oracle Gateway will be explained later). Services_name is the service name provided by the remote server. For example: The remote server Remotea, the Oracle database is installed, its listening port is 1521, and the database service name is ORCL the above statement is rewritten as follows:

Create Database link dblinkname dblinkname connect to username identified by password using ' Nsnname=

(

address_list=

(

(Adress= (PROTOCOL=TCP) (Host=remotea) (port=1521))

)

(Connect_data= (ORCL))

)’

Well, here's a simple description of how Oracle Database link types and access types, and how to create database link.

Then, use the following: (under the condition that the access rights are met)

Select * FROM [email protected];

Where Remotetablename is the name of the table in the remote database to be accessed, Dblinkname is both the database connection we created earlier. It's a bit cumbersome to write every time you access a table, and for a normal user, such an interface might not be available to us, in other words, we might just let the user know what table or view is there. This time we can use aliases (synonym) to simplify/hide the interface, the syntax is as follows

CREATE [public] Synonym_name

for [schema.] object_name[@database_link_name];

Synonym supported objects:

? Tables

? Types

? Views

? Materialized views

? Sequences

? Procedures

? Functions

? Packages

There is not much to explain here, we create a synonym for the database link we created earlier, the code is as follows:

Create public tableName for [email protected];

Once created, we can then access the tables in the remote database:

Select * from TableName;

The above statement is equivalent to Select * from [email protected];

Thanks,

Oracle Database Links parsing