Oracle9i IMP-20 On Import Of Table With TIMESTAMP Column That Was Created Via Database Link (Doc ID 286597.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 9.2.0.6 - Release: 8.1.7 to 9.2
Information in this document applies to any platform.
***Checked for relevance on 12-DEC-2011***

Symptoms

In a remote Oracle8i or higher release database, you have a table with a column of the TIMESTAMP data type:

connect scott/tiger
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SSXFF';
create table my_original_tab
(
   nr number,
   stamp_col timestamp(3)
);

insert into my_original_tab values (1, '2004-10-20 18:34:31.123456');
commit;
select * from my_original_tab;

NR         STAMP_COL
---------- -----------------------
         1 2004-10-20 18:34:31.123

In your local Oracle8i or Oracle9i database you have created a database link:

connect system/password
grant create database link to scott;

connect scott/tiger
alter session set nls_timestamp_format = 'MON DD, YYYY HH:MI:SSXFF AM';
create database link orcl.oracle.com connect to scott identified by tiger using 'orcl';

Now you create a new table in this database with the CREATE TABLE AS SELECT statement (CTAS), accessing the original table via the database link:

create table my_tab as select * from my_original_tab@orcl.oracle.com;
select * from my_tab;

NR         STAMP_COL
----------  ----------------------------
         1 OCT 20, 2004 06:34:31.123 PM

You export this table:

#> exp scott/tiger file=exp_tab.dmp log=exp_t.log tables=my_tab

...
About to export specified tables via Conventional Path ...
. . exporting table          MY_TAB          1 row exported
Export terminated successfully without warnings.

Any you try to import from this export dump file, e.g. into a different user:

#> imp system/password file=exp_tab.dmp log=imp_t.log fromuser=scott touser=hugo

...
. importing SCOTT's objects into HUGO
. . importing table "MY_TAB"
IMP-00020: long column too large for column buffer size (7)
Import terminated successfully with warnings.

- Increasing the value for the Import BUFFER parameter does not solve the IMP-20 error.
- Pre-creating the table in the target schema and running import with IGNORE=Y does not solve the IMP-20 error.
- Specifying Import parameter COMMIT=Y does not import any row for this table.

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