DataPump Import (IMPDP) Fails For Table With Column Datatype LONG With Error ORA-31696 (Doc ID 305819.1)

Last updated on AUGUST 12, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 12-Aug-2013***

Symptoms

DataPump import fails with error ORA-31696 while loading data into pre-existing table, if there is a LONG column in that table.
This is demonstrated by the following example:

connect / as sysdba

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

create or replace directory tmp as '/tmp';
grant read, write on directory tmp to test;

connect test/test

-- create table with LONG column
create table a_tab
(
   id     number,
   text_v varchar2(10),
   text_l long
);
alter table a_tab add constraint a_tab_pk primary key (id);

-- populate the table
begin
  for i in 1..10 loop
    insert into a_tab values (i, 'Text '||lpad (to_char (i), 5, '0'), 'Text LONG '||lpad (to_char (i), 990, '0'));
  end loop;
  commit;
end;
/

set long 1000

select * from a_tab;

ID         TEXT_V
---------- ----------
TEXT_L
--------------------------------------------------------------------------------
         1 Text 00001
Text LONG 0000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
....
0000000000000000000000000000000000000001

         2 Text 00002
Text LONG 0000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000002
....

10 rows selected.


Export the table with:

#> expdp test/test directory=tmp dumpfile=a_tab.dmp content=data_only tables=a_tab logfile=expdp_a_tab.log


Then:

truncate table a_tab;


and import the data with:

#> impdp test/test directory=tmp dumpfile=a_tab.dmp full=y table_exists_action=append logfile=impdp_a_tab.log


This fails with error:

Import: Release 10.2.0.1.0 - 64bit Production on Friday, 09 March, 2012 9:58:40

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=tmp dumpfile=a_tab.dmp full=y table_exists_action=append logfile=impdp_a_tab.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31696: unable to export/import TABLE_DATA:"TEST"."A_TAB" using client specified AUTOMATIC method
Job "TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 09:58:43

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