RE: "Best" way to copy schema data to another DB
- From: "Michael Dinh" <mdinh@xxxxxxxxx>
- To: <rjoralist@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Apr 2010 09:07:29 -0700
I have used datapump most of the time.
cat expdp.par
DIRECTORY=data_pump_dir
SCHEMAS=expschema
DUMPFILE=expschema.dmp
METRICS=y
LOGFILE=expschema.log
EXCLUDE=statistics
cat impdp.par
DIRECTORY=data_pump_dir
DUMPFILE=expschema.dmp
LOGFILE=impdp_newschema.log
METRICS=y
SCHEMAS=expschema
REMAP_SCHEMA=expschema:newschema
REMAP_TABLESPACE=expschema:newschema
Instead of suing INSERT APPEND, try use CTAS.
If you can use transportable tablespace, then that is the way to go.
Unfortunately, it does not work for us because it fails validation.
NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
individual or entity to which it is addressed, and may contain
information that is privileged, confidential and exempt from disclosure
under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN
PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU
ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT
DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS
EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of
this email (and attachments) is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited. Please notify the sender of the
error and delete the e-mail you received. Thank you.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rich Jesse
Sent: Monday, April 12, 2010 8:39 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: "Best" way to copy schema data to another DB
Hey all,
Using 10.1 EE (and hopefully going to 11.x sometime this year), we
currently use exp/imp to copy "simple" schema data (heap tables,
indexes, PKs, and a few LOBs) from a Production DB to a Development DB.
My biggest problem with this approach is the redo generation in the Dev
DB -- imp can't APPEND.
So I should use expdp/impdp, right? Not without the expensive Advanced
Compression option I can't. My 200GB export compresses to 9GB with gzip
and we don't have 300GB of disk (planning for growth) to "waste". I've
also tried using the NETWORK_LINK option of impdp, but it refuses to use
APPEND.
I was hoping to work around this by scripting a set of INSERT
/*+APPEND*/ SELECT statements using the same db link I created for the
impdp test above.
The problem with that approach is the occasional LOB column.
With exp "going away", we're losing major functionality. I'm thinking
the most viable option I have is to write some Perl equivalent of
exp/imp to get the job done *correctly* (with on-the-fly [de]compression
and the APPEND hint).
Thoughts on my options?
Rich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
