4 Replies Latest reply on Mar 13, 2012 11:48 AM by meet_sanc

    How to move only subset of data from one database to another?

      The requirement details are as below,
      1) The database contains static as well as transactional data for telecom domain.
      2) We have to move region-wise data from one database to another.
      3) There are around 10 regions.
      4) The region wise data extraction from source db is based on around 40 to 50 tables. Rest of the tables contains
      static data & it will not change.
      5) The volume is around 1 million subscribers per region.
      6) The migration is required because
      a) The client is upgrading its base product which uses this database
      b) There is a change in structure of static tables
      c) Hardware upgrade
      d) The client want to start with single region on new database & rest of the regions will be operated from old
      7) Keep execution time to very minimum.

      I am thinking to have solution as below,
      1) Create destination database with upgraded db structure (as mentioned in 6b)
      2) Create database links to access source db in destination db.
      3) Write SQL queries to fetch data from all the respective tables for a specific region
      4) Write separate PL/SQL blocks for each table to fetch data from source db & insert into respective table in
      destination db
      a) Use FOR ALL & bulk collect to improve the performance
      b) Divide table data into multiple chunks & execute parallel batches (around 10) to insert the data
      5) Validate pre & post counts to verify the success of migration

      Is there any other better way?