Re: export/import puzzler

Once bitten, thrice shy. You mention two different versions of Oracle. That's two different versions of export/import.

You also mention doing the install of 8.1.7 on a different machine after the original export was done. Good, if so... but are you sure?

In other words, my suspicion is that you are encountering export and import version issues. Lower versions of export work against a higher database version, but not the other way around. Just make sure. If that isn't it, my apologies.

Other things to check: no-one's played around with editing the dump file, have they? Big no-no if so. Try doing an import of just the one table causing the problem, having first created it manually, and then doing import with ignore=y.

Regards
HJR "Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3ce109a6.92704221_at_ausnews.austin.ibm.com...
> 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.)
Received on Sat May 18 2002 - 21:16:12 CDT