咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

Oracle 在导入时得到错误IMP-32 SQL statement exceeded buffer length

Oracle 在导入时得到错误IMP-32 SQL statement exceeded buffer length

Posted by PDSERVICE on 五月 27, 2016 In

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

 
oerr imp 32
00032, 00000, "SQL statement exceeded buffer length"
// *Cause:  The buffer was too small for the SQL statement being read.
// *Action: Rerun with a larger buffer. This can also be an indication of a 
//          corruption in the import datafile.
 
oerr imp 8
00008, 00000, "unrecognized statement in the export file: \n  %s"
// *Cause:  Import did not recognize a statement in the export file. Either 
//          the export file was corrupted, or an Import internal error has 
//          occurred. 
// *Action: If the export file was corrupted, retry with a new export file. 
//          Otherwise, report this as an Import internal error and submit
//          the export file to customer support.

 

 

 

适用于：

Oracle Database – Enterprise Edition – 版本9.2.0.1及以上
本文信息适用于任何平台。
*** 09-Jul-2014检查相关性***

症状

你尝试使用原始导入工具（IMP）导入一个导出dump，收到错误：

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file

我们来看一个演示的例子。

— create a big table (with a large DDL, 1000 columns) in schema test
connect test/test
create table big_table
(
col000000000000000000000000001 varchar2(4000) not null,
col000000000000000000000000002 varchar2(4000) not null,
col000000000000000000000000003 varchar2(4000) not null,
col000000000000000000000000004 varchar2(4000) not null,
col000000000000000000000000005 varchar2(4000) not null,
col000000000000000000000000006 varchar2(4000) not null,
col000000000000000000000000007 varchar2(4000) not null,
col000000000000000000000000008 varchar2(4000) not null,
col000000000000000000000000009 varchar2(4000) not null,
col000000000000000000000000010 varchar2(4000) not null,
…. 1000 columns
col000000000000000000000001000 varchar2(4000) not null
);

— insert one row into table
declare
i number := 1;

begin
insert into big_table values
(
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
lpad (to_char (i), 1000, ‘0’),
…. 1000 columns
lpad (to_char (i), 1000, ‘0’)
);
commit;
end;
/

导出表，使用：

#> exp test/test file=big_table.dmp tables=big_table

结束显示：

Export: Release 11.1.0.7.0 – Production on Mon Jun 22 11:52:53 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
. . exporting table          BIG_TABLE           1 rows exported
Export terminated successfully without warnings.

Drop schema TEST 中的表并从written dump导入它，使用：

#> imp test/test file=big_table.dmp tables=big_table

结束显示：

Import: Release 11.1.0.7.0 – Production on Mon Jun 22 11:53:55 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing TEST’s objects into TEST
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
R2(4000) NOT NULL ENABLE, “COL000000000000000000000000945” VARCHAR2(4000) NOT
NULL ENABLE, “COL000000000000000000000000946” VARCHAR2(4000) NOT NULL ENABLE, “C
OL000000000000000000000000947” VARCHAR2(4000) NOT NULL ENABLE, “COL0000000000000
00000000000948″…
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000…
. importing TEST’s objects into TEST
Import terminated successfully with warnings.

原因

内部BUFFER （默认基于OS，例如32 KB，如果未在imp 命令行指定）也用于将DDL语句从导出dump文件传输到Oracle服务器。如果DDL 超过BUFFER 长度，则生成IMP-32 。

要获取在导出（源）使用的DDL长度，发出语句：:

select dbms_lob.getlength (dbms_metadata.get_ddl (‘TABLE’, ‘BIG_TABLE’, ‘TEST’)) “SQL_LENGTH” from dual;

SQL_LENGTH
———-
67270

1 row selected.

该情况下的DDL语句长度是 67270 字节，超过了默认 BUFFER 长度（30720 字节 ~ 32 KB）。

解决方案

增加BUFFER大小：

#> imp test/test file=big_table.dmp tables=big_table buffer=100000

导入成功：

Import: Release 11.1.0.7.0 – Production on Mon Jun 22 12:10:15 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing TEST’s objects into TEST
. importing TEST’s objects into TEST
. . importing table          “BIG_TABLE”          1 rows imported
Import terminated successfully without warnings.

参考

http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.ht...