Re: still more problems

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA702686E00_at_lnewton.leeds.lfs.co.uk...
> Hi Chuck,
>
> I presume you include me in 'nobody can stand him' - sorry, you cannot
> speak for me so don't try. (Are you american by the way ?)
>
> As for your original problem getting indexes and constraints applied -
> did you get it sorted yet ? I appear to be missing quite a few replyes
> and postings at the mometn so I'm not sure.
>

Yes I managed to get it all sorted out. I manually fixed all of the things that IMP didn't do right. I manually added the grants that IMP failed to pick up. I manually created datafiles that it should have sized correctly but didn't. I manually added the column defaults that IMP skipped completely. I manually added some constraints that IMP missed. There is nothing tricky about any of this stuff so I'm still left wondering why exp/imp did not pick them up when these are exactly the things the tool is supposed to do - a logical export and import. There were no warnings or error messages indicating any of the stuff that was missed or incorrectly done. I had to use a 3rd party tool when everything was done to identify everything the IMP missed.

BTW the actual command used for the import included IGNORE=Y. I failed to include that in the OP.

This was all done for a rather large project that I was part of. I and 3 other DBAs were using exp/imp to move several large and vary large databases from an SGI server to an IBM RS6000 running AIX. At the end of 19 hours of copying data on about 12 databases, we all noticed things that IMP simply failed to do. I am seriously considering writing my own exp/imp shell scripts for the next time we go through something like this.

We also encountered another very strange problem with one large index. Normally when you have a unique index and then issue an ALTER TABLE ADD ... PRIMARY KEY... it simply converts the UI to a PK constraint in a fraction of a second. On one index this did not happen. The index was valid but the ALTER went right into a series of full table scans on a 63m row table. We killed it after about 2 hours and tried again. Same thing started to happen. Placing a NOVALIDATE on the ALTER solved the problem but again we are left scratching our heads as to why this happened at all. Numerous other UIs were converted to PKs without the NOVALIDATE. Is there a limit on the number of columns for converting a UI to PK? This table has 9 columns in the PK.

--
Chuck