IMP-00020 error

Posted on 1998-12-22
Last Modified: 2012-06-21
I use Personal Oracle 7.3. I exported user to us.dmp file. One user´s table is called "RZK", has 10,000 rows and one it´s column is LONG ROW type. Average size of this column is 5 kB. I try import user back to Oracle using imp73 utility. The command line is following:
"imp73 system/manager file=us fromuser=user touser=user buffer=32000"
From RZK table is exported 1521 rows and then error is occured:
"IMP-00020 long column too large for column buffer size (22) "
In oracle help is recomended to increase the insert buffer size 10,000 bytes step-by-step. In command line I was increasing buffer parametr up to 100,000. Error stays the same. I´m not sure I change the right parametr. Could somebody give me an advice .

   Thank you very much.
Question by:ivano
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1083430
Try it with 1 000 000 (1 million bytes)
I'm importing an Oracle 7.3.4 Database (exported) into an Oracle 8 database via imp80
and I use 1000000 as buffersize without problems. My opionion is that as long as the Operating System has enough memory you can increase this value.


Author Comment

ID: 1083431
I tried to import database into Oracle with
buffer size 1 000 000 and 5 000 000. The result is the same. Interesting thing is that
any size I choose, at error message "IMP-00020 long column too large for column buffer size (22) " is always indicate the same size (22).


Expert Comment

ID: 1083432
What are the exact versions of the Oracle database for export and the Oracle database you import.
My opionon is that the target database must have at least the same version number (or higher) as the source database.

Version 7.3 isnot enough information, Version 7.3.4 is e.g. a full version information

I do an export from Oracle 7.3.4 (under HP Unix) to Oracle 8.0.3 under Win NT 4 Server.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

ID: 1083433
Database was exported from Oracle 7.3.4 under WinNT workstation and I want to import it into Oracle 7.3.4 under WinNT workstation.


Expert Comment

ID: 1083434
Sorry, no more good ideas
only some points you should think about:
Is the target database big enough (e.g. temp tablespace, system tablespace) ?
Set the values/sizes according to the source database.

Expert Comment

ID: 1083435
I found not so good news for you. The Oracle Utilities Manual says:

"Importing LONGs
Warning: LONG columns can be up to 2 gigabytes in length. Because they can be exported in sections, Oracle LONGs are always exportable. On import, however, LONGs require contiguous memory. Therefore, it is not always possible to export LONG columns from one operating system and import them on another system. Even on the same system, memory limitations may make it impossible to import very large LONG columns that were successfully exported on that system."


Accepted Solution

junfeb earned 200 total points
ID: 1083436
Are you very sure that your long column is only about 5000 bytes. What about the rest of the record . What is your average row size.

What is your SGA size. Increase your SGA size so that oracle gets that much amount of memory. Make sure that much free memory is available on the system, so that oracle can grab it. With the NT operating system oracle touches only a minimum amount of pages. You can even set the initialization parameter PRE_PAGE_SGA = true.
Warning : Set PRE_PAGE_SGA only if you have enough memory on the machine. Also setting pre_page_sga will increase startup time as it touches all of the pages and brings it into memory.

Increase your SGA and try to do the import again. I've imported records greater than 32K and not had any problems.


Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question