Mark,
With due respect to the good advice you offered, your comments sort of
drifted a little off the guys original question. A couple of extra
points need adding if he's going to take your advice, which I've
inserted below in the reply you gave. Note that I'm not disagreeing
with any of the advice you gave :-)
Quote:>Migration is a great oppurtunity to re-org your entire database.
But ONLY if this is NEEDED. Hence my warnings in my original reply.
Quote:>Our DBA group
>has used platform migrations more than once to re-define the entire data base.
As above. Don't let DBA's run away with a db until they've spoken with
the designers. I'm lucky, I end up doing both jobs, so I get a lot of
control.
Quote:>As it is much faster to import without indexes we predefine all our
>application owners and tables.
Which assumes the guy has the original SQL to create the tables in the
appropriate tablespaces already sat on his system. If not, he can grep
them from the export file. Note that the 'clean up' of the storage
parameters can take quite some time for a large number of tables, and
the initial and next will always appear as rounded byte values, no
matter how you originally defined them (K or M)
It's for reasons like this I use D2K. It's worth it's weight in gold,
especially for larger/more complex databases. You can manage the
entire db from a decent tool and dramitically reduce the amount of
messing around you have to do with imp, exp and sql scripts.
Quote:>Then we import the data with indexes=n,
>ignore=y, and constraints=n. Then we build our indexes from a script. Follow
>this with a full import with rows=n to get the constraints, packages, and
>public synonyms. This way you have total control of table and index placement
>and sizing.
Again, only if you have all the original SQL or a database design
tool. And again, you can get it from exp files, but be prepared to do
a lot of work for a large(ish) database.
Quote:>Doing the export/import this way can reduce the total time
>involved for a sizable database from 1/2 to 2/3 (Unix to Unix via pipes).
Yep, and that's usually down to the index builds. Maybe worth
mentioning also that bitmapped indexes build much, much faster and use
much less space. So now may be the time to also assess whether they
would be of use on the system (as, of course, they may not).
Quote:>Anyway, no matter how you do your import I would like to suggest that you
>consider creating a special rollback segment to support your import. You can
>force the import to use this segment by taking all others offline.
Or just commit after each buffer fill on the import. We have tables
which are many 10's of Gigs in size, and we could not dedicate that
much rollback just for imports.
Quote:>Mark Powell -- Oracle 7 Certified DBA
>- The only advice that counts is the advice that you follow so follow your own
>advice -
MotoX
(Oracle 7 and Oracle 8 Certified Pro.)
by