Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem Exporting/Importing a Table

Posted on 2008-06-19
10
Medium Priority
?
1,307 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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
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…

721 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