如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]
适用于：
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.htm#SUTIL1705
Leave a Reply