Solved

IMP-00020 error

Posted on 1998-12-22
7
3,213 Views
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.
0
Comment
Question by:ivano
7 Comments
 
LVL 2

Expert Comment

by:liebla
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.

0
 

Author Comment

by:ivano
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).

0
 
LVL 2

Expert Comment

by:liebla
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ivano
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.


 
0
 
LVL 2

Expert Comment

by:liebla
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.
0
 
LVL 3

Expert Comment

by:poncejua
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."

0
 
LVL 3

Accepted Solution

by:
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.

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now