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

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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

740 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