Solved

Problem Exporting/Importing a Table

Posted on 2008-06-19
10
1,253 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sum of columns in a row in oracle 3 44
oracle date format checking 7 34
oracle forms question 22 48
PL/SQl Expanding the WHERE statement in query 3 34
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 …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

730 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