My Oracle Support Banner

Importing A Table With A BLOB Or CLOB Using A Synonym Causes ORA-1403 (Doc ID 744725.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version: to - Release: 10.2 to 10.2
Oracle Server - Enterprise Edition - Version: to   [Release: 11.1 to 11.1]
Information in this document applies to any platform.


If a table contains a LOB column and a synonym exists on table then import will fail with ORA-1403 error, like the following test case demonstrates:

connect /as sysdba
grant dba to test identified by test;
grant dba to syn identified by syn;

connect test/test
create table emp (col_a number, col_b varchar2(3), c_lob clob);

INSERT INTO EMP VALUES (180, 'a', '01010101010101');
INSERT INTO EMP VALUES (181, 'a', '01010101010101');
INSERT INTO EMP VALUES (182, 'a', '01010101010101');
INSERT INTO EMP VALUES (183, 'a', '01010101010101');
INSERT INTO EMP VALUES (184, 'a', '01010101010101');
INSERT INTO EMP VALUES (185, 'a', '01010101010101');

host exp test/test file=EMP TABLES=EMP

connect syn/syn
create synonym EMP for test.EMP;
delete from EMP;

host imp syn/syn file=EMP TABLES=EMP ignore=y

IMP-00058: ORACLE error 1403 encountered
ORA-01403: no data found 6 rows imported
Import terminated successfully with warnings.

Without the CLOB datatype in the table EMP, the ORA-1403 does not occur.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.