Use Database Link to export data

 

Record the problems encountered during the last data import. ServerOracleVersion10.1.0.4, LocalOracleVersion10.2.0.1To export several tables to the local machine. The table containsBlobField. Direct useExpCommand, error:

 

EXP-00056: Oracle error 6550 encountered

ORA-06550: Line 1, column 41:

PLS-00302: component 'set _ no_outlines 'must be declared

ORA-06550: Line 1, column 15:

PL/SQL: Statement ignored

EXP-00000: Export terminated unsuccessfully

 

I thought I had no permission, so I changedDBAThe Role user still reports the error.GoogleIn the following section:

 

Applies:

Oracle Server-Enterprise Edition-version: 10.2.0.1.0

This problem can occur on any platform.

 

Symptoms

While trying to export 9.2.0.6 database with 10.2.0.1.0 exp Utility

Receive following errors :-

 

Export: Release 10.2.0.1.0-production on Tue Oct 25 17:11:46 2005

Copyright (c) 1982,200 5, Oracle. All rights reserved.

EXP-00056: Oracle error 6550 encountered

ORA-06550: Line 1, column 41:

PLS-00302: component 'set _ no_outlines 'must be declared

ORA-06550: Line 1, column 15:

PL/SQL: Statement ignored

EXP-00000: Export terminated unsuccessfully

Cause

Use of higher version export utility (10.2.0.1) on lower version database (9.2.0.6 ).

 

This problem occurs when using 10.2 exp utility for 10.1, 9.2, 8.1.7 databases.

 

Solution

 

As per compatibility matrix, you need to use the export utility of the lower version of source and target database.

 

It seems that10.2OfExpCommand cannot be exported9.2. There are two principles summarized by old birds on the Internet:

 

1. Export the data with the export utility of the lowest database version involved.

 

2. Import the data with the import utility of the target database.

 

Try to change the Client Version later11.1.0.6.

 

Try againPL/SQL developerIs exportedPDDEFormat, but an error occurred while importing, not becauseBlobData, but the field name in the table is usedOracleKeyword,PL/SQL developerIt seems that it cannot be processed. Even if you have created a table in advance, you cannot create a table when importing data. It seems that this road cannot be achieved.

What should I do? Suddenly thinkInsert into <Table> select * from <Table>AndCreate Table <Table> as select * from <Table>These twoSQLStatement. Then, how does the Local Machine access the table on the server (Database Link). Establish a data link on the local machine to access the server, and then access the server table just like accessing the local table. In the createDatabase LinkI also encountered a small problem, using the configured network service name:

 

Create Database LinkLocaltest
Connect ToTest
Using 'Test' ;

 

It cannot be connected, but changed to the following method:

 

Create database link localtest
connect to test
using '(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.1.2) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)' ;

 

The connection is successful. The reason is unknown.

 

Next, it's easy to use tables.Insert into <Table> select * from <user>. <Table >@< dblink>, No table usedCreate Table <Table> as select * from <user>. <Table >@< dblink>. Note that the table created by the second statement will lose the primary key, constraint, and index. Therefore, it is best to create a table first and then use the first statement to export data. Another question is whether this method can export a large amount of data.OracleAn error occurred while handling too many cursors. Tested at least each table11 WThere is no problem with multiple records. Of course you have to wait for a while.