Importing A Table With A BLOB Or CLOB Using A Synonym Causes ORA-1403

(Doc ID 744725.1)

Last updated on DECEMBER 02, 2011

Applies to:

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

Symptoms

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');
commit;

host exp test/test file=EMP TABLES=EMP

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

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.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms