如何解决IMP-00020错误
Posted: December 16, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: IMP-00020 | Leave a comment »这里我重现了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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V
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类型就改为4000，char类型就改为2000
2、 改完后重新在odu里unload上述导不进去的表，注意此时一定不要再执行unload dict
3、 在目标库里先建立待导入数据的表，并将其有问题的列的长度先改大
4、 最后再以ignore=true方式执行imp导入数据
最后我们可以看到，现在表TEST已经可以成功导入：
$ imp \’sys/oracle@testdb as sysdba\’ file=SYS_TEST.dmp tables=test ignore=true
Import: Release
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database
With the Partitioning, OLAP and Data Mining options
sys@TESTDB>select * from test;
C1
—————————————————————————————————-
aa
Recent Comments