Solved

Problem Exporting/Importing a Table

Posted on 2008-06-19
10
1,178 Views
Last Modified: 2013-12-19
Hello everyone.  I have a table, which contains roughly 3000 rows.  I am able to do a full export of the table with no error messages, but when I try and import the export file, I get this error:

imp-00064: Definition of LOB was truncated by export
IMP-00028: partial import of previous table rolled back: 3011 rows rolled back

If I query the table in question after the import, there are no rows.

If I Google this error, it says "Delete the offending row in the exported database and retry the export".  I was wondering how would I know what the offending row is if I don't get any errors during the export?  Thanks in advance.
0
Comment
Question by:marksmithy69
10 Comments
 
LVL 8

Expert Comment

by:LindaC
Comment Utility
You are not telling the version of the source and target databases if different this can be the cause:


You are probably getting this error due to differences in your Oracle versions. As a good rule of thumb, use the lower version of export to create the dump file. Then use the destination version of imp for the import process. For instance, if I am copying data from a 10g database to an Oracle 8i database, I use the 8i export utility and the 8i import utility. If I am copying data from a 9i database to a 10g database, I use the 9i export utility and the 10g import utility. import utility.
0
 
LVL 1

Author Comment

by:marksmithy69
Comment Utility
The Oracle versions are exactly the same - 8.1.7.0.0.  I am actually doing a full database export/import.  The other 20 or so tables in the export are imported fine.  This is the only table that is causing any problems.  Thanks.
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
The error was actually on the export.  It wasn't able to write the full object out on at least one of the rows.  The "offending row" was already rolled back based on the messages you posted (it's the one in the imported table). You should try the export again and then import again.  if there's still a problem, then we need to look at your export params and environment.

Good luck!
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
This appears to be LOB related and perhaps the export of the offending row is skipped due to space contraint.

If your filesystem or an old version of gzip (if you are using it to
compress your export files) cannot handle files larger than 2GB, than
this can be the cause of this issue.

Try to generate a log file during the export so that it could be examined for any errors.

R.
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.

 
LVL 1

Author Comment

by:marksmithy69
Comment Utility
I think the error may be due to corruption.  I just checked the latest database export log, and noticed that there is an error while exporting this particular table:

EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 9, block # 765232)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 9: 'E:\CPSDB\DATA\CPSLOB01.ORA'
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
And this line here:

ORA-26040: Data block was loaded using the NOLOGGING option

is bad news.  That means you weren't logging changes and you can do a "recover tablespace" to fix it.  Do you have any backups?  There's no way of knowing when the corruption occurred, but you might get lucky.  You could also try deleting some of the more recent entries (after you do a file-image backup) and retry the export to see if you can salvage something.

Good luck!
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
Actually, that should read "CAN'T" do a recovery.
0
 
LVL 22

Expert Comment

by:DrSQL
Comment Utility
marksmithy69,
    It's been over a month.  Could you please update/close this question?  Thank you for using Experts Exchange.

Good luck!
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (125)

Computer101
EE Admin
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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

772 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

15 Experts available now in Live!

Get 1:1 Help Now