Wednesday, November 17, 2010

Import Error: IMP-00032: SQL statement exceeded buffer length

While trying to import a big table with 1000s of partitions I got error:

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_PART_1" NOLOGGING COMPRESS, PARTITION "TEST_NOV_1" VALUES LESS THAN (2010112806) PCTFREE 0 PCTUSED 40...

The table was exported simiply with:

exp system/oracle file=tab.dmp tables=table_part

While run import:

imp system/oracle file=tab.dmp fromuser=scott touser=tiger

I got the above error.
I tried various combination of parameter buffer i.e.

imp system/oracle file=tab.dmp fromuser=scott touser=tiger buffer=1000000 commit=y

etc, but still got the error. So I wanted to know whats the value required for buffer.

Solution:
In the source database we need to run:

SQL> select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','table_part','SCOTT')) "SQL_LENGTH" from dual;

SQL_LENGTH
----------
3886502

The above output gives the value of buffer we need to have atleast to run the import, so I used:

imp system/o1ympic file=tabs.dmp log=tabs.log fromuser=SCOTT touser=TIGER commit=y ignore=Y buffer=44886502


This solved the problem.
Metalink Note: 846397.1

2 Comments:

Debasis Das said...

This solution is of no use, as in most of the cases while importing you will not have access to the source schema from which the file is exported.

Apun Hiran said...

Well in most cases you would take a export dump and import it elsewhere, incase you dont have access to the source, then yes this wont solve your problem.
Regards
Apun