| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: IMPORT - how many passes?
overlooked one thing, if it is across different database version, and u did
a full export, then import that time u must specify all the users excluding
the SYSTEM at the imp commandline, full import may add new tables or
synonyms definition that may not be needed in the new database.
Peter Teoh
HP: 91286341
"Peter Teoh" <no_email_please_at_singnet.com.sg> wrote in message
news:boagth$7ik$1_at_reader01.singnet.com.sg...
To prove my statements, here I have extracted out all the necessary portion
in full export file (right hand side is the line number):
Note that in line nos 19386 it is inserting, and the constraints is not created yet, only in line nos 31348 it is created, and turned on only at the end.
19383 ALTER TABLE "STATS$SYSTEM_EVENT" ADD CONSTRAINT
"STATS$SYSTEM_EVENT_PK"
PRIMARY KEY ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "EVENT") USING INDEX
PCTFREE
10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST
GROUPS
1) TABLESPACE "PERFSTAT_TS" LOGGING ENABLE
19384 TABLE "STATS$TEMPSTATXS"
19385 CREATE TABLE "STATS$TEMPSTATXS" ("SNAP_ID" NUMBER(6, 0) NOT NULL
ENABLE,
"DBID" NUMBER NOT NULL ENABLE, "INSTANCE_NUMBER" NUMBER NOT NULL ENABLE,
"TSNAM
E" VARCHAR2(30) NOT NULL ENABLE, "FILENAME" VARCHAR2(513) NOT NULL ENABLE,
"PHYR
DS" NUMBER, "PHYWRTS" NUMBER, "SINGLEBLKRDS" NUMBER, "READTIM" NUMBER,
"WRITETIM " NUMBER, "SINGLEBLKRDTIM" NUMBER, "PHYBLKRD" NUMBER, "PHYBLKWRT" NUMBER, "WAIT_
31348 ALTER TABLE "STATS$TEMPSTATXS" ADD CONSTRAINT "STATS$TEMPSTATXS_FK"
FORE
IGN KEY ("SNAP_ID", "DBID", "INSTANCE_NUMBER") REFERENCES "STATS$SNAPSHOT"
("SNA
P_ID", "DBID", "INSTANCE_NUMBER") ON DELETE CASCADE ENABLE NOVALIDATE
31349 ENDTABLE
31350 TABLE "STATS$WAITSTAT"
145433 ENDTABLE
145434 TABLE "STATS$TEMPSTATXS"
145435 ALTER TABLE "STATS$TEMPSTATXS" ENABLE CONSTRAINT
"STATS$TEMPSTATXS_FK"
145436 ENDTABLE
145437 TABLE "STATS$WAITSTAT"
145438 ALTER TABLE "STATS$WAITSTAT" ENABLE CONSTRAINT "STATS$WAITSTAT_FK"
145439 ENDTABLE
-- Peter Teoh HP: 91286341 "Peter Teoh" <no_email_please_at_singnet.com.sg> wrote in message news:boagg0$752$1_at_reader01.singnet.com.sg... 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.Received on Wed Nov 05 2003 - 04:39:15 CST
. 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.
 |
 |