Re: export/import puzzler

Ed Stevens wrote:

> Platform: Ora 8.0.5 EE and 8.1.7 EE on NT
>
> Given an 8.0.5 database, we took a full export with the following parms:
>
> userid=SYSTEM/******@WRPTDB
> FULL=Y
> FILE=F:\ORAEXP\WRPT\WRPTFULL.DMP
> LOG=F:\ORAEXP\WRPT\FULL.LOG
> RECORDLENGTH=4096
> BUFFER=20480
> ROWS=Y
> GRANTS=Y
> INDEXES=Y
> COMPRESS=N
> CONSTRAINTS=Y
>
> Then we installed 8.1.7 on a clean system, built the db (but did not create a
> user schema) and ran an import with the following parm file:
>
> userid=system/******@wrptdb
> FROMUSER=NMM
> TOUSER=NMM
> FILE=F:\ORAEXP\WRPT\WRPTFULL.DMP
> LOG=F:\ORAEXP\WRPT\ImpNMM.LOG
> RECORDLENGTH=4096
> BUFFER=40960
> ROWS=Y
> GRANTS=Y
> INDEXES=Y
> IGNORE=Y
> COMMIT=Y
>
> On the first attempt, the first 2 tables loaded fine, but the third started
> throwing
>
> IMP-00019: row rejected due to ORACLE error 1400
> IMP-00003: ORACLE error 1400 encountered
> ORA-01400: cannot insert NULL into ("NMM"."CAL_DAY"."CAL_DAY_NME")
>
> Very puzzling, seeing as how the table definition that created the NOT NULL
> constraint came from the same export that created the supposedly null data.
>
> Knowing it wasn't a real solution, we truncated the 3 tables that had been
> touched, removed the NOT NULL constraint from the problem table, and re-ran the
> import. This time it got to the 3d table, pegged the CPU, and seemed to just
> sit there. I left it overnight, then killed it this morning. (It didn't respond
> to ctrl-C, so I used NT Task Mgr to knock it down.) This action did not allow
> the process to close the log file, so it still shows a length of zero and is
> thus unreadable. A SELECT COUNT on the problem table returned a count of over
> 38 MILLION rows, when we know the original only had around 14,000 rows.
>
> So . . . . lets
> SELECT
> COMMENTS,
> IDEAS,
> CRITICISM,
> SOLUTION
> FROM
> NEWSGROUP_PARTICIPANTS;
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

I'd start by making a new export and making sure that the export and import tool versions correspond with Oracle's recommendation for how to do this between versions.

If that doesn't work ... how many tables and how much data? There are many other ways.

Daniel Morgan Received on Tue May 14 2002 - 10:30:04 CDT