Wednesday, November 26, 2008

Dumping Oracle schema

Here's the link to dump an Oracle schema:
http://www.tek-tips.com/viewthread.cfm?qid=1301609&page=34

Command
(sample format)exp buffer= compress= grants= feedback= consistent= file= log= owner= userid=/@

(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

"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 "..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 "_Exp.log"."owner=" : Name of source schema to export."userid=" : connect string for user officiating the export.

To import:
http://www.oracle.com/technology/products/text/x/Samples/Exp_Imp/index.html
Sample:
imp userid=samples/samples file=test.dmp show=n log=test.txt
imp sys/sys file=c:\dmp.dmp fromuser=user1 touser=userA