exp/imp an entire schema 2

[ksbrace]

Hello,
I am looking to export a 9i db schema and import it into a 10g db. Are there any caveats doing this?

exp schema/password FILE=schema.dmp OWNER=schema

Also, should I pass any other parameters? Thanks in advance!
Kelly

 

[karluk]

I would routinely add the following parameters:

log=logfilename,consistent=y

"log" writes the output of the export, including errors, to a log file. It is useful for debugging in case the export fails. "consistent" causes Oracle to make a read-consistent export. If you don't use it and several tables in the schema get updated during the export, the export dump will most likely contain the updates to some of the tables, but not all of them

 

[SantaMufasa]

Kelly,

These are the typical parameters that I use for an export ("exp") command line:

(sample format)
exp buffer=<value> compress=<y | n> grants=<y | n> feedback=<n lines> consistent=<y | n> file=<dump-filename> log=<log-filename> owner=<schema to export> userid=<username of exporter>/<password>@<tns alias>

(example invocation with sample values)
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.TEST.dump log=TEST_Exp.log owner=TEST userid=TEST/TEST@dhunt

Here is a brief explanation (You can see a complete list of exp parameters if you enter at the command prompt, "exp help=y"):

"buffer=" : give a large value, which speeds the export

"compress=" : "Y" means 'allocate as an initial extent an extent size that accommodates the ENTIRE contents of the table'. "N" means 'use the standard "initial" and "next" parameters for the table. (I use "N" to avoid receiving the Oracle "Unable to allocate initial extent..." error.)

"grants=" : "Y" means export the grant on this object; "N" means don't bother exporting grants. Use "N" if the other users that have grants to the source schema's objects in the source database do not exist in the target database.

"feedback=" : During the export, display to the screen, a dot (".") for every "n" records exported.

"consistent=" : "Y" means do not allow in-flight transactions to cause contents of tables to become inconsistent during the export.

"file=" : This is the name you give to the dump file. I usually use the format "<db-name>.<schema-name>.dump".

"log=" : This is the name you give to the file that documents the progress (and errors, if applicable) that occur during the export. I usually use the format "<schema-name>_Exp.log".

"owner=" : Name of source schema to export.

"userid=" : connect string for user officiating the export.

These are the typical parameters that I use for an import ("imp") command line:

(sample format)
imp buffer=<value> grants=<y | n> feedback=<n lines> fromuser=<source schema-name> touser=<target schema-name> file=<dump-filename> log=<log-filename> owner=<schema to export> userid=<username of importer>/<password>@<tns alias>

(example invocation with sample values)
imp buffer=15000000 grants=y feedback=1000 fromuser=TEST touser=TEST file=DHUNT.TEST.dump log=TEST_Imp.log  userid=TEST/TEST@dhunt

Here is a brief explanation of parameters which differ from the "exp" parameters, above. (You can see a complete list of imp parameters if you enter at the command prompt, "imp help=y"):

"fromuser=" : Name of source schema (from your 9i database).

"touser=" : Name of target schema (in your 10g database).

Additional notes: When you are migrating data from an earlier-version database to a later-version database, export with earlier-version "exp" executables and import with later-version "imp" executables.

If you are "downgrading" data from a later-version database to an earlier-version database, you must perform both the export and import using earlier-version executables.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

I don't type as fast as KarlUK...I promise that I started my response while there were still no other responses.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[johnpau80]

START USING THE NEW 10g DATA PUMP EXPORT ITS REALLY COOL.


EXPDP HELP=Y

IMPDP HELP=Y