Oracle ORA-22992 problem

Create / * source only*/ table mingshuo.tmp_ms_19031403 as select * from backupwt.tmp_ms_19031403@dblk_e1

An error occurred while using the above relocation data via dblink

ORA-22992: cannot use LOB locators selected from remote tables

SQL >! oerr ora 22992

22992, 00000, "cannot use LOB locators selected from remote tables"

/ / * Cause: A remote LOB column cannot be referenced.

/ / * Action: Remove references to LOBs in remote tables.

You can see that this is caused by the lob field in the source table.

There are two simple solutions to this error.

1. Global temporary table

two。 Materialized view

Let's first look at the method of the first global temporary table:

Now the target side establishes the target table structure:

Create / * source only*/ table mingshuo.tmp_ms_19031403 as select * from backupwt.tmp_ms_19031403@dblk_e1 where 1: 0

The destination side establishes a global temporary table:

-- Create table

Create / * source only*/ global temporary table mingshuo.gb_temp_tab

(

Id NUMBER (20) not null

. . .

) on commit delete rows

Insert into mingshuo.gb_temp_tab select * from backupwt.tmp_ms_19031403@dblk_e1

Be careful not to submit the data after inserting it into the temporary table, otherwise the data will be gone

Insert data from the temporary table into the target table:

Insert into mingshuo.tmp_ms_19031403 select * from mingshuo.gb_temp_tab

Commit

The second way to materialize the view

Pass the data locally through the materialized view.