Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3282
  • Last Modified:

IMP-00020 error

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.
0
ivano
Asked:
ivano
1 Solution
 
lieblaCommented:
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.

0
 
ivanoAuthor Commented:
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).

0
 
lieblaCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ivanoAuthor Commented:
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.


 
0
 
lieblaCommented:
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.
0
 
poncejuaCommented:
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."

0
 
junfebCommented:
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.

Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now