Thursday, April 18, 2013

Using Datapump to move a table from Production to Development Database

Yeah nothing special but good to have it in here, hopefully beginners could get some help.

Prod -->
Take a backup at source database (in this case Production)
expdp username/password directory=dumpdir dumpfile=somefilename.dmp logfile=somefilename.log tables=schema.table_name

successful execution of the expdp command will create dump files in the directory "dmpdir" pointing to a location on disk(server).
scp those files from source to target (development database in our case).
$scp *dmp targetserver:/location_on_server

use the above dumps to import the tables in an existing same schema with a new name

impdp username/password directory=dmpdir dumpfile=abovedumpfilename.dmp logfile=somefilename.log
tables=earlier_username.table_name remap_table=earlier_username.table_name:new_table_name

Also if there are multiple dumpfiles you could use the following syntax

impdp username/password directory=dmpdir dumpfile=abovedumpfilename%U.dmp logfile=somefilename.log tables=earlier_username.table_name remap_table=earlier_username.table_name:new_table_name






No comments:

Post a Comment