Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


IMP-00020 error

Posted on 1998-12-22
Medium Priority
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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 400 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 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