Solved

Problem Exporting/Importing a Table

Posted on 2008-06-19
10
1,286 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
[X]
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
10 Comments
 
LVL 8

Expert Comment

by:LindaC
ID: 21822682
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
ID: 21823085
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
ID: 21823153
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 16

Expert Comment

by:Richard Olutola
ID: 21824838
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
 
LVL 1

Author Comment

by:marksmithy69
ID: 21825847
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
ID: 21825951
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
ID: 21825955
Actually, that should read "CAN'T" do a recovery.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 22088388
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
ID: 22586591
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

615 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