Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › IMP-00032&IMP-00008
发新帖
gtlions

207

积分

1

好友

4

主题

IMP-00032&IMP-00008

1#
发表于 2012-1-13 10:50:22 | 查看: 14461| 回复: 15
源端HPUX，目标端RHL，客户端win2003,DB 10g，由于都是在客户端操作的，因此我没有列出具体的源端和目标端信息。
从一个user模式导出的dmp中执行tables模式导入，提示：

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

解决办法是增加imp参数 buffer=81920
爱老婆，爱FM，爱音乐；挨踢，爱折腾，爱Oracle
2#
发表于 2012-1-13 10:52:31
buffer默认依赖于操作系统：
BUFFER

Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.

win2003下默认值是exp是4096，imp是8192，见如下测试：
C:\Users\Administrator>exp

Export: Release 10.2.0.1.0 - Production on 星期五 1月 13 10:14:36 2012

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


用户名: system@test2.4
口令:

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
输入数组提取缓冲区大小: 4096 > ^C

D:\>imp

Import: Release 10.2.0.1.0 - Production on 星期五 1月 13 10:16:44 2012

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

用户名: gtlions@test2.4
口令:

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

导入文件: EXPDAT.DMP> 1.dmp

输入插入缓冲区大小 (最小为 8192) 30720>^

回复 只看该作者 道具 举报

3#
发表于 2012-1-13 10:54:38
我操作的表结构如下：

文档中有描述的计算字段的如下：
Example: Calculating Buffer Size

This section shows an example of how to calculate buffer size.

The following table is created:

CREATE TABLE sample (name varchar(30), weight number);


The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.

Therefore, the maximum row size is 56 (30+2+22+2).

To perform array operations for 100 rows, a buffer size of 5600 should be specified.

([I}I2B0VS29QOXIN9O@_)R.jpg (15.48 KB, 下载次数: 551)

([I}I2B0VS29QOXIN9O@_)R.jpg

回复 只看该作者 道具 举报

4#
发表于 2012-1-13 10:55:28
按照文档描述我的表远远没有超过8192啊，怎么出现这个提示，而且提示的字面意思似乎是sql语句。。。。。

回复 只看该作者 道具 举报

5#
发表于 2012-1-13 14:54:12
纠正下：buffer默认值是exp是4096，imp是30720

回复 只看该作者 道具 举报

6#
发表于 2012-1-13 15:06:45
经过验证，至少从我这次验证来看是由于exp出来的文件中的表的创建语句太长导致的，我做了个测试，创建一个分区表，逐步增加分区数量，然后做导出和导入操作。

回复 只看该作者 道具 举报

7#
发表于 2012-1-13 15:07:23
在最后一次导入操作中出现了这个错误：
  1. [oracle@localhost test]$ imp gtlions/gtlions file=exp.dmp tables=t_largesql_test

  3. Import: Release 10.2.0.4.0 - Production on Fri Jan 13 15:04:11 2012

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


  8. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
  9. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  11. Export file created by EXPORT:V10.02.01 via conventional path
  12. import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
  13. . importing GTLIONS's objects into GTLIONS
  14. IMP-00032: SQL statement exceeded buffer length
  15. IMP-00032: SQL statement exceeded buffer length
  16. IMP-00008: unrecognized statement in the export file:
  17.   
  18. Import terminated successfully with warnings.
复制代码

回复 只看该作者 道具 举报

8#
发表于 2012-1-13 15:08:25
增加buffer imp成功：
  1. [oracle@localhost test]$ imp gtlions/gtlions file=exp.dmp tables=t_largesql_test buffer=81920

  3. Import: Release 10.2.0.4.0 - Production on Fri Jan 13 15:07:37 2012

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


  8. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
  9. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  11. Export file created by EXPORT:V10.02.01 via conventional path
  12. import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
  13. . importing GTLIONS's objects into GTLIONS
  14. . importing GTLIONS's objects into GTLIONS
  15. IMP-00015: following statement failed because the object already exists:
  16. "CREATE TABLE "T_LARGESQL_TEST" ("ID" NUMBER(10, 0) NOT NULL ENABLE, "PHONE_"
  17. 。。。
  18. 。。。
  19. Import terminated successfully with warnings.
  20. [oracle@localhost test]$
复制代码

回复 只看该作者 道具 举报

9#
发表于 2012-1-13 15:12:13
现在的问题是怎么认定建表语句长度呢？刚刚在可以执行导入成功的情况下（没有使用buffer，默认应该是30720），使用Length(Dbms_Metadata.Get_Ddl('TABLE', 'T_LARGESQL_TEST'))查看了下是34000。可能是从dmp文件中有别的计算语句的长度还是？使用show=y的情况下，排版是个大问题，难道就是使用show=y出来看到的文本？

回复 只看该作者 道具 举报

10#
发表于 2012-1-13 19:58:18
ODM Data:
  1. OERR: IMP 32 "SQL statement exceeded buffer length" [ID 21664.1]

  3. Error:  IMP 32
  4. Text:   SQL statement exceeded buffer length
  5. -------------------------------------------------------------------------------
  6. Cause:  The buffer was too small for the SQL statement being read in
  7. Action: Rerun with a larger buffer. This may also be an indication of a
  8.         corruption in the import data file




  13. IMP-32 Obtained During Import [ID 846397.1]


  16. Applies to:

  18. Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
  19. Information in this document applies to any platform.
  20. Symptoms

  22. You try to import an export dump using the original import urility (imp) and received the errors:

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

  27. Let's follow the next example that demonstrates this.

  29. -- create a big table (with a large DDL, 1000 columns) in schema test
  30. connect test/test
  31. create table big_table
  32. (
  33.    col000000000000000000000000001 varchar2(4000) not null,
  34.    col000000000000000000000000002 varchar2(4000) not null,
  35.    col000000000000000000000000003 varchar2(4000) not null,
  36.    col000000000000000000000000004 varchar2(4000) not null,
  37.    col000000000000000000000000005 varchar2(4000) not null,
  38.    col000000000000000000000000006 varchar2(4000) not null,
  39.    col000000000000000000000000007 varchar2(4000) not null,
  40.    col000000000000000000000000008 varchar2(4000) not null,
  41.    col000000000000000000000000009 varchar2(4000) not null,
  42.    col000000000000000000000000010 varchar2(4000) not null,
  43.    .... 1000 columns
  44.    col000000000000000000000001000 varchar2(4000) not null
  45. );

  47. -- insert one row into table
  48. declare
  49.   i number := 1;
  50.    
  51. begin
  52.   insert into big_table values  
  53.   (
  54.      lpad (to_char (i), 1000, '0'),
  55.      lpad (to_char (i), 1000, '0'),
  56.      lpad (to_char (i), 1000, '0'),
  57.      lpad (to_char (i), 1000, '0'),
  58.      lpad (to_char (i), 1000, '0'),
  59.      lpad (to_char (i), 1000, '0'),
  60.      lpad (to_char (i), 1000, '0'),
  61.      lpad (to_char (i), 1000, '0'),
  62.      lpad (to_char (i), 1000, '0'),
  63.      lpad (to_char (i), 1000, '0'),
  64.      .... 1000 columns
  65.      lpad (to_char (i), 1000, '0')
  66.   );   
  67.   commit;   
  68. end;
  69. /

  71. Export the table with:

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

  75. This ends with:

  77. Export: Release 11.1.0.7.0 - Production on Mon Jun 22 11:52:53 2009

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

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

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

  89. Drop the table in schema TEST and import it back from written dump with:

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

  93. This ends with:

  95. Import: Release 11.1.0.7.0 - Production on Mon Jun 22 11:53:55 2009

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

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

  102. Export file created by EXPORT:V11.01.00 via conventional path
  103. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
  104. . importing TEST's objects into TEST
  105. IMP-00032: SQL statement exceeded buffer length
  106. IMP-00032: SQL statement exceeded buffer length
  107. IMP-00008: unrecognized statement in the export file:
  108. R2(4000) NOT NULL ENABLE, "COL000000000000000000000000945" VARCHAR2(4000) NOT
  109. NULL ENABLE, "COL000000000000000000000000946" VARCHAR2(4000) NOT NULL ENABLE, "C
  110. OL000000000000000000000000947" VARCHAR2(4000) NOT NULL ENABLE, "COL0000000000000
  111. 00000000000948"...
  112. IMP-00032: SQL statement exceeded buffer length
  113. IMP-00008: unrecognized statement in the export file:
  114. 000000000000000000000000000000000000000000000000000000000000000000000000000000
  115. 00000000000000000000000000000000000000000000000000000000000000000000000000000000
  116. 00000000000000000000000000000000000000000000000000000000000000000000000000000000
  117. 000000000000000...
  118. . importing TEST's objects into TEST
  119. Import terminated successfully with warnings.
  120. Cause

  122. The internal BUFFER (default 32 KB, if not specified in imp command line) is also used to transfer the DDL statements from export dump file to Oracle server. If the DDL exceeds the BUFFER length, then IMP-32 is raised.

  124. To obtain the DDL length run at export (source) site the statement:

  126. select dbms_lob.getlength (dbms_metadata.get_ddl ('TABLE', 'BIG_TABLE', 'TEST')) "SQL_LENGTH" from dual;

  128. SQL_LENGTH
  129. ----------
  130.      67270

  132. 1 row selected.

  134. The length of the DDL statement is in this case 67270 bytes, what exeeds the default BUFFER length (30720 bytes ~ 32 KB).
  135. Solution

  137. Increase the BUFFER size:

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

  141. and import succeeds:

  143. Import: Release 11.1.0.7.0 - Production on Mon Jun 22 12:10:15 2009

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

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

  150. Export file created by EXPORT:V11.01.00 via conventional path
  151. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
  152. . importing TEST's objects into TEST
  153. . importing TEST's objects into TEST
  154. . . importing table          "BIG_TABLE"          1 rows imported
  155. Import terminated successfully without warnings.



  159. IMP-32 and IMP-8 When Doing an Import With a too Small (Default) Buffer Size [ID 146319.1]



  163. Applies to:

  165. Oracle Server - Enterprise Edition - Version: 8.0.5.0 to 11.2.0.2 - Release: 8.0.5 to 11.2
  166. Information in this document applies to any platform.
  167. Symptoms

  169. You are doing an import with parameter SHOW=Y option and receive the following errors:

  171. #> imp test/test file=test.dmp full=y show=y

  173. Import: Release 11.1.0.7.0 - Production on Thu Feb 26 16:43:24 2009

  175. Copyright (c) 1982, 2007, Oracle. All rights reserved.
  176. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
  177. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  178. Export file created by EXPORT:V11.01.00 via conventional path
  179. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
  180. . importing TEST's objects into TEST
  181. IMP-00032: SQL statement exceeded buffer length
  182. IMP-00032: SQL statement exceeded buffer length
  183. IMP-00008: unrecognized statement in the export file:
  184. R2(4000) NOT NULL ENABLE, "COL000000000000000000000000945" VARCHAR2(4000) NOT
  185. NULL ENABLE, "COL000000000000000000000000946" VARCHAR2(4000) NOT NULL ENABLE, "C
  186. OL000000000000000000000000947" VARCHAR2(4000) NOT NULL ENABLE, "COL0000000000000
  187. 00000000000948"...
  188. IMP-00032: SQL statement exceeded buffer length

  190. Cause

  192. Even with the SHOW=Y option used, the import utility verifies the syntax of all the SQL DDL statements in the dump file. Import uses default buffer size of 64K.

  194. If the SQL statement in dump file exceeds the default buffer size, then the buffer size needs to be increased to accommodate this, otherwise the imp utility won't be able to load the full SQL statement into the buffer and raise IMP-32 and IMP-8.

  196. Solution

  198. Increase the buffer size, other than the default (64K):

  200. #> imp test/test file=test.dmp full=y show=y buffer=1000000
复制代码

回复 只看该作者 道具 举报

11#
发表于 2012-1-13 20:00:45
Cause:  The buffer was too small for the SQL statement being read in

buffer  过小会导致 imp时的SQL 语句无法读入

The length of the DDL statement is in this case 67270 bytes, what exeeds the default BUFFER length (30720 bytes ~ 32 KB).
Solution

默认的 Buffer length 是 32KB

你的情况 Length(Dbms_Metadata.Get_Ddl('TABLE', 'T_LARGESQL_TEST'))查看了下是34000

34000 > 32KB = 32 *1024= 32768

回复 只看该作者 道具 举报

12#
发表于 2012-1-13 21:10:27
怎么你演示的有2个长度啊？
前面：The internal BUFFER (default 32 KB, if not specified in imp command line)
后面：Even with the SHOW=Y option used, the import utility verifies the syntax of all the SQL DDL statements in the dump file. Import uses default buffer size of 64K.
是说在show=y的情况下是64k的吗？

回复 只看该作者 道具 举报

13#
发表于 2012-1-13 21:26:01
BUFFER   

Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.


buffer 的默认值是  由操作系统平台决定的。

[oracle@vrh2 ~]$ imp

Import: Release 11.2.0.3.0 - Production on Sat Jan 14 00:21:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

Import data only (yes/no): no >

Import file: expdat.dmp >

Enter insert buffer size (minimum is 8192) 30720>


Linux 上默认是 30720  ，手头暂时没有其他操作系统 可供测试

回复 只看该作者 道具 举报

14#
发表于 2012-1-13 23:36:25
buffer 的默认值是  由操作系统平台决定的--这个我上面也说到了。
我想问的是你提供的资料后面的当中的一句话：
  1. Even with the SHOW=Y option used, the import utility verifies the syntax of all the SQL DDL statements in the dump file. Import uses default buffer size of 64K.

  3. If the SQL statement in dump file exceeds the default buffer size, then the buffer size needs to be increased to accommodate this, otherwise the imp utility won't be able to load the full SQL statement into the buffer and raise IMP-32 and IMP-8.

  5. Solution

  7. Increase the buffer size, other than the default (64K)
复制代码


这个64K似乎是不同的选项的说法？

回复 只看该作者 道具 举报

15#
发表于 2012-1-13 23:44:39

回复 14# 的帖子

我不这样认为  

可能在少数平台上  BUFSIZE 默认是64K

但目前没有环境可供测试 ， 当然这只是我的猜测

回复 只看该作者 道具 举报

16#
发表于 2012-1-14 09:26:37
ok，了解了，不过开贴之前还算是被我蒙对了处理问题的方向和原因，嘿嘿

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 01:44 , Processed in 0.067058 second(s), 25 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569