Solved

Problem Exporting/Importing a Table

Posted on 2008-06-19
10
1,275 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

728 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