如何解决IMP-00020错误

这里我重现了IMP-00020并且给出了解决方案,如下是整个的测试过程:

sys@TESTDB>create table test(c1 varchar2(1));

 

Table created.

 

sys@TESTDB>insert into test values(‘a’);

 

1 row created.

 

sys@TESTDB>commit;

 

Commit complete.

 

sys@TESTDB>col c1 format a2

sys@TESTDB>select * from test;

 

C1

a

 

sys@TESTDB>select c1,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from test;

 

C1 LOCATION

— ———————————————————————————

a  1_61826

 

sys@TESTDB>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

通过BBED把上述行记录由’a’改为’aa’。改完后执行ODU并以dmp格式unload test中的数据:

ODU> unload table sys.test

 

Unloading table: TEST,object ID: 51307

Unloading segment,storage(Obj#=51307 DataObj#=51307 TS#=0 File#=1 Block#=61825 Cluster=0)

1 rows unloaded

 

ODU> exit

$ cd data

$ ls -lrt

total 80

-rwxr-xr-x    1 oracle   dba             809 Apr 28 2010  SCOTT_EMP.txt

-rwxr-xr-x    1 oracle   dba             280 Apr 28 2010  SCOTT_EMP.sql

-rwxr-xr-x    1 oracle   dba             454 Apr 28 2010  SCOTT_EMP.ctl

-rw-r–r–    1 oracle   dba            1628 Sep 07 10:25 SYS_PROPS$.txt

-rw-r–r–    1 oracle   dba             128 Sep 07 10:25 SYS_PROPS$.sql

-rw-r–r–    1 oracle   dba             314 Sep 07 10:25 SYS_PROPS$.ctl

-rw-r–r–    1 oracle   dba            1267 Oct 19 17:17 SYS_UNDO$.txt

-rw-r–r–    1 oracle   dba             572 Oct 19 17:17 SYS_UNDO$.sql

-rw-r–r–    1 oracle   dba             613 Oct 19 17:17 SYS_UNDO$.ctl

-rw-r–r–    1 oracle   dba            4096 Oct 25 18:53 SYS_TEST.dmp

 

此时执行imp操作则重现了imp-00020错误:

$ imp \’sys/oracle@testdb as sysdba\’ file=SYS_TEST.dmp tables=test ignore=true

 

Import: Release 10.2.0.1.0 – Production on Mon Oct 25 19:00:09 2010

 

Copyright (c) 1982, 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

 

Export file created by EXPORT:V08.01.07 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

export client uses ZHS16GBK character set (possible charset conversion)

. importing SYS’s objects into SYS

. importing SYS’s objects into SYS

. . importing table                         “TEST”

IMP-00020: long column too large for column buffer size (1)

Import terminated successfully with warnings.

由此可见buffer size中的参数就是原来表里某列的长度,这里1就是指表test中的列c1.

 

解决方法如下:

1、  先改ODU的数据字典文件col.odu,将目标表的有问题的列的长度改大,比如varchar2类型就改为4000char类型就改为2000

2、  改完后重新在oduunload上述导不进去的表,注意此时一定不要再执行unload dict

3、  在目标库里先建立待导入数据的表,并将其有问题的列的长度先改大

4、  最后再以ignore=true方式执行imp导入数据

 

最后我们可以看到,现在表TEST已经可以成功导入:

$ imp \’sys/oracle@testdb as sysdba\’ file=SYS_TEST.dmp tables=test ignore=true

 

Import: Release 10.2.0.1.0 – Production on Mon Oct 25 19:09:43 2010

 

Copyright (c) 1982, 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

 

Export file created by EXPORT:V08.01.07 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

export client uses ZHS16GBK character set (possible charset conversion)

. importing SYS’s objects into SYS

. importing SYS’s objects into SYS

. . importing table                         “TEST”          1 rows imported

Import terminated successfully without warnings.

 

$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Oct 25 19:09:50 2010

 

Copyright (c) 1982, 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

 

sys@TESTDB>select * from test;

 

C1

—————————————————————————————————-

aa



Leave a Reply

Your email address will not be published. Required fields are marked *