Shulou(Shulou.com)06/01 Report--

In the project, it is necessary to import the data from the old system into the new system. It is decided to import the old data into the target database by using the data link dblink. The operation process is recorded as follows:

1. Creating a Dblink

create database link ygbgtest_portaltest_link

connect to dbuser identified by password

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xx)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)';

2. Query with linked list

Execute SQL select * from yggtest_portaltest_link@portal_information;

Error "ORA-02019: Connection description for remote database not found." Check and find that the table name and the data chain name are written backwards. After adjustment, execute select * from portal_information@ygbgtest_portaltest_link;

Error "ORA-22992: Cannot use LOB locator selected from remote table." The reason for the error is that the query source data table contains CLOB type fields.

3. Solve dblink query source data table contains large field problem

My solution to this problem is to create temporary tables and import data from the source data tables into the temporary tables. Then query the temporary table for CLOB field data.

--Create temporary tables to get remote table data

create global temporary table temp_ygbg_information on commit preserve rows

as select * from portal_information@ygbgtest_portaltest_link;

select count(1) from temp_ygbg_information t;

--Insert data from temporary table into destination table

insert into portal_information

(id,

title,

picture_url,

status,

author_id,

author_name,

create_time,

modify_date,

delete_date,

view_num,

order_flag,

summary,

type,

promulgation_charge,

information_source,

sort_num,

sub_title,

is_slidenews)

select

SEQ_PORTAL_INFORMATION.NEXTVAL,

title,

picture_url,

status,

author_id,

author_name,

create_time,

modify_date,

delete_date,

view_num,

order_flag,

summary,

type,

promulgation_charge,

information_source,

sort_num,

sub_title,

is_slidenews from temp_ygbg_information t1 where t1.id=3338;

--View data in large field

select dbms_lob.substr(t.summary,4000,1) ty,t.* from portal_information t where t.id=3338;

Since then, querying and retrieving table data from the source database and inserting it into the target database through Dblink have been performed properly. Of course, there are other ways to view large fields online, such as using views.

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.