Home » RDBMS Server » Server Administration » Moving subsets of data between servers (Any)
Moving subsets of data between servers [message #436721] Sat, 26 December 2009 11:32 Go to next message
JustinBailey
Messages: 14
Registered: December 2009
Junior Member

I have a fairly complicated database (around 300+) tables and I need to move portions of those tables to another database instance.

My client has all his customer's data in one big database. One of those customers decided that for security reasons, they wanted their database onsite. Now I have to move all the data, for just that customer, to another database. This customer's data is spread all over the 300+ tables and it's fairly normalized so lots of parent-child relationships, etc...

Has anyone ever tried to do something like this? Do I just have to write scripts/sql to do it manually? I was hoping for something easier than just writing all the sql.

Cheers,
Justin
Re: Moving subsets of data between servers [message #436722 is a reply to message #436721] Sat, 26 December 2009 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have all the tables an identifier like customer id to identify the rows?

Regards
Michel
Re: Moving subsets of data between servers [message #436723 is a reply to message #436722] Sat, 26 December 2009 11:40 Go to previous messageGo to next message
JustinBailey
Messages: 14
Registered: December 2009
Junior Member
Yes,
The tables all have a surrogate key, a single numeric primary key.
Re: Moving subsets of data between servers [message #436724 is a reply to message #436723] Sat, 26 December 2009 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you can use exp with the query clause on this id.
C:\>exp help=y

Export: Release 10.2.0.4.0 - Production on Sam. DÚc. 26 18:59:24 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

Regards
Michel
Re: Moving subsets of data between servers [message #436725 is a reply to message #436724] Sat, 26 December 2009 12:24 Go to previous messageGo to next message
JustinBailey
Messages: 14
Registered: December 2009
Junior Member
expdp will certainly work, but for a large hierarchy of tables it will be cumbersome.

for example:
patient ->HAS MANY-> diagnosis ->HAS MANY-> diagnosis_details

I was just hoping there was something better than a Perl script...but if not, Perl + expdp is a good option.

[Updated on: Sat, 26 December 2009 13:18] by Moderator

Report message to a moderator

Re: Moving subsets of data between servers [message #436727 is a reply to message #436725] Sat, 26 December 2009 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So ALL tables do NOT reference the top level customer data otherwise it is possible to do it in a single data pump job.

Regards
Michel
icon6.gif  Re: Moving subsets of data between servers [message #436821 is a reply to message #436725] Mon, 28 December 2009 12:33 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You could explore data-extract/migration tools like Data Bee or other.
Wink
Re: Moving subsets of data between servers [message #436842 is a reply to message #436821] Mon, 28 December 2009 22:02 Go to previous messageGo to next message
gtylar
Messages: 1
Registered: December 2009
Junior Member
Check out relationalmigration.com
I don't know how well it performs with HUGE data sets, but it works well for us. We use it to move configuration data from a development environment to production.
Re: Moving subsets of data between servers [message #437054 is a reply to message #436721] Wed, 30 December 2009 12:35 Go to previous message
JustinBailey
Messages: 14
Registered: December 2009
Junior Member
DataBee or Relational Migration look like a perfect fit.

DataBee is a little weird with 4 separate applications, and it crashed in windows 7, but worked in my xp VM.

Relational Migration looks pretty good. I'm going to give it a try. I'll post any results.
Previous Topic: Oracle DBMS SCHEDULER (merged 3)
Next Topic: Rename datafile in Production when using Physical Standby
Goto Forum:
  


Current Time: Fri Dec 09 15:11:20 CST 2016

Total time taken to generate the page: 0.27182 seconds