Shulou(Shulou.com)05/31 Report--

This article is to share with you what are the problems encountered in using DBLink. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

1. Create DBLink

1.1 creation command

create database link zlbak connect to zlbak01 identified by his using 'orcl';

Note that the orcl in quotes is the service name configured on the database server, not the client's native configuration.

1.2 name

If the parameter global_names is True, the DBLink name required to be created must be the same as the global_name of the library being connected.

create database link orcl connect to zlbak01 identified by his using 'orcl';

The linked library, global_name, can be very long, for example:

select * from global_name;

ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

You can shorten the name by removing the character after the dot with the following command:

update global_name set global_name = 'orcl';

Note that it must not be changed to empty, otherwise it will cause the database to fail to start, and special methods are needed to solve it.

If you modify the domain name in the following way, there will still be a domain name after the modification.

alter database rename global_name to orcl

When global_names is True, what if you want to create multiple DBLinks pointing to the same library and cannot duplicate names?

create database link orcl@link1 connect to zlbak01 identified by his using 'orcl';

create database link orcl@link2 connect to zlbak01 identified by his using 'orcl';

It turns out that you need to add @ to GLOBAL_NAME and an identifier.

Since global_names is so troublesome when it is True, can it be changed to false?

If you don't copy the stream, it's completely possible. Modify it:

alter system set global_name=false;

After modification, restart the database settings to take effect. After changing to false, the name of DBLink can be arbitrarily selected.

1.3 special case

I remember that in 2014, I did the historical data transfer of the first user. After completion, I was ready to query the remote history database through DBLink. I encountered a strange problem.

DBLink created by the following command:

create database link zlbak connect to zlbak01 identified by his using 'orcl';

Note that the orcl in quotes is the service name configured on the database server, not the client's native configuration.

After it is created, it cannot be used normally, and an error is reported when executing the query:

select * from Personnel table @zlbak

ORA-12543: TNS: Unable to connect to target host.

On the server, using the tnsping service name orcl is common.

In sqlplus through zlbak01 user connection orcl can also log in normally.

The user environment is 10.2.0.5 Windows 64bit.

After a night, I finally succeeded in changing it to the following way.

create database link zlbak connect to zlbak01 identified by his using

'(DESCRIPTION = (ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)

(HOST=192.1.68.1.1)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=orcl)))';

2. Query remote data via DBLink

2.1 Querying remote tables with LOB type fields is not supported

When a remote table contains large data type fields such as blob,clob,xmltype,long, etc., an error will be reported when querying through DBLink:

ORA-22992: Cannot use LOB locator selected from remote table

There are currently two known practices:

a. Object table plus function conversion

source end

1)Create OBJECT type, the fields are the same as the original table, only change the LOB field to Varchar2(4000)

2)Create a Table of this type

3)Create a read conversion function that inserts the data from the original table into the Table type and returns

In cursor loop mode, dbms_lob.getlength and dbms_lob.substr are used to take up to 4000 characters at a time, output by Pipe Row pipeline function, autonomous transaction is required, and the function returns the pre-commit transaction.

4)Create a view, call the function, and convert it to a two-dimensional table with a Table statement.

destination side

1) Define the same OBJECT type and Table type as the source

2) Create a receive conversion function to convert Varchar2(4000) received from the source to LOB type after splicing.

Autonomous transactions are required

3) Create a view, query the data through DBLink, and convert it to LOB fields through the function created in the previous step.

4) Query this view, just like querying table data, can return LOB field data normally.

Although this method can be implemented, but because of the view plus function mode, can not use the index, so, can only be used as a temporary query means, and implementation is more troublesome.

b. temporary table

Although it is not supported to query LOB type fields directly through DBLink mode, insert into …Select mode is supported. Therefore, you can create a temporary table on the local server, insert the data from the remote data table of the query into the temporary table, and then, through the temporary table, you can normally access the table containing LOB type fields.

This approach is much simpler to implement than "object tables plus conversion functions," but it is important to use indexes.

2.2 Specify the drive table via driving_site

Many technicians in the user environment, through DBLink to associate query some business system remote data table, but most people may not notice a problem: remote data table full table replication.

You may think that it will use indexes like local tables, in fact, it is likely that no index is used at all, do not believe you can immediately look at those SQL execution plans, pay attention to the analysis of which the operation is Remote rows, that is, full table replication.

When accessing a remote data table, you can use the index directly if you specify a value directly in the query condition for the index-related field.

For example:

Select id from H Patient Order Record where Patient id=:v1;

Where H Patient Order Record is a view connected to a remote database table via DBLink.

However, there are several common cases where indexes of remote tables are not utilized:

1) Connection of local and remote tables

2) Connection of Table memory table and remote table

3) Index-related query conditions used Or

For example:

Select b.Id, b. Category, c. Name As Category Name, b. Name, b. Specimen Site

From H Patient Order Record A, Item List B, Item Category C,

Table(f_Num2list('43190722, 43190723')) D

Where a.Id = d.Column_Value And a. Treatment Item id = b.Id And b. Category = c. Code

In this case, all the data in the remote data table will be queried and transmitted to the local server, and then the table connection will be performed.

When the remote table is a large table, the local table is a small table, and the relational query needs to decide the direction of data replication, which is a problem that exists in distributed data access.

Add the prompt word/*+driving_site(a)*/after Select. This way, you can specify the remote table as the driver table, copy the local small table to the remote, so that you can use the index, and avoid copying the large table data to the local server.

However, if there is a Table memory table, it is still impossible to use the index, because the data of the memory table does not support copying to the remote as a driven table, you can rewrite the query and avoid using the Table method. For example, if the condition value is directly passed in in the in mode, the problem is that binding variables cannot be used. For low-frequency business such as historical data query, it is acceptable not to use binding variables.

Note that driving_site is not valid for dml (insert,delete,update), which drives SQL plans with the library where the target table resides.

There are also the following cases, although they are all remote table connections, but because of the use of Or, resulting in the implementation plan does not use the index, can be adjusted to expand Or, written in Union All mode.

Select Distinct b. Send Number, b. Sent By As Person, b. Send Time As Time

From H Patient Order Record A, H Patient Order Send B

Where a.Id = b. Order id And (a.Id = 43895356 Or a. Related id = 43895356)

Order By Time Desc

2.4 Connection needs to be closed after querying DBLink

After query is executed through db-link, the connection from the current session to the remote database will not be closed automatically, which may cause Session flooding of the target database in connection pool-based management, thus consuming process resources.

In this case, you can execute the Close Connection command after the query is complete:

alter sesssion close database link orcl;

Note that the commit command needs to be executed first. You can also use system packages to close connections:

DBMS_SESSION.CLOSE_DATABASE_LINK(orcl);

2.5 commit the transaction

After executing queries via dblink in PL/SQL developer,

Commit and rollback light up. Why?

The following tests reveal some patterns:

select count(1) from test@dblink;

No commit prompt generated

select * from test@dblink;

Commit prompt will be generated

select * from test@dblink where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.