Re: IMPORT - how many passes?

ONLY ONE PASS IS NEEDED. just do a simple full=y full export, without all the other options. And then take a look at the output file using a hex editor. The full export file will contain all your answers u can ever asked. Or "vi" will do. There may be binary data, but in general ALL SQL is always in ASCII. And look at the sequence and types of SQL INSIDE THE EXPORT FILE. Or alternatively look at the message generated by exp as shown below.

u can see that Oracle is SMART ENOUGH to export all the constraints AFTER the tables creation, and later turning on the constraints after it has been created. This of course assume that the original exported database's integrity constraint is valid.

. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
.....many other schema...

. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs

--
Peter Teoh
HP: 91286341
"Walt" <walt_at_boatnerd.com> wrote in message
news:3FA7DDC1.FD5D8A9A_at_boatnerd.com...

I'm trying to import all the schemas* from our production database into
a new instance (which will eventualy beome the new production database
once I get it all to work).

On the new installation I create the database, create the tablespaces,
create the users/schemas giving them quotas on the tablespaces they
need.  So far so good.

Next I want to run import - how many passes is typical for this?  I
understand I can't do it in one pass, beause I'll be trying to grant
rights to objects that don't exist yet, and apply foreign keys to tables
where the parent data may not exist yet.

It seems like I need three passes, but I keep reading that a two-pass
import is the usual way to go.    Here's what I think I need:

 1) rows=n, constraints=n, grants=n, indexes=n
 2) rows=y, constraints=n, grants=y, indexes=n, ignore=y
 3) rows=n, constraints=y, show=y, grants=n, indexes=n, ignore=y

Step three should build a text file that can be used to create the
constraints and indexes.  Is this the way to do it, or am I barking up
the wrong tree?

Or alternatively, anybody got a standard set of parfiles for doing this
sort of thing?


details:
Current production Oracle 8.1.6 on winNT
New database Oracle 9.2 on Win2k
The export file is from a full export, size just under 2 Gig.


--
file://-Walt
//
//


*all the schemas == all our application schemas
  - not system, mdsys, ctxsys, etc.