Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Question Unanswered: How to move table data from one oracle server to anothe/remoter oracle server

    How to move specified table data from one oracle server/machine to remote / another oracle server/machine.

    Does Database Link option help us to get connection between servers and moving data / tables from source to target?

    If yes, means how to do it periodically?

    And also I need to purge the old data (whichever moved) from source server.

    Over all my expectation is that, moving & keeping all reports data from source servers to target server. We have 5 source servers/oracle Dbs to one Target Oracle server.


    Please help me in this reg.

    Thanks
    Suresh

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Export & import utilities might help; if your database supports it, Data Pump might be an advanced option.

    Database link? Sure, why not.

    Periodically? Perhaps you should establish database links and use materialized views (with refresh option set to desired value).

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    Question Merging/Moving data from one oracle server to another

    Ok, now the term 'periodic' can be hold. We decided to run a query / plsql srcipt to migrate data and purge after a period of time..

    Currently we have the setup like

    Example

    Oracle Servr1
    database1

    tableA
    ID, NUMBER (PK)
    NAME VARCHAR2(20)
    DP_ID NUMBER (FK)

    TableB

    DP_ID NUMBER (PK)
    DP_NAME Varchar(10)
    AUDIT DATE


    (Each table contains 5,000 rows , CRUD operations happened over these tables by Java application , say hibernate / JPA , which inter tern contains Pk,FK , Indexes constraints)
    ===============


    Oracle Servr2
    database1

    tableA
    ID, NUMBER (PK)
    NAME VARCHAR2(20)
    DP_ID NUMBER (FK)

    TableB

    DP_ID NUMBER (PK)
    DP_NAME Varchar(10)
    AUDIT DATE

    (Each table contains 10,000 rows , CRUD operations happened over these tables by Java application , say hibernate / JPA , which inter tern contains Pk,FK , Indexes constraints)

    I want to move or copy / merge data from server1 & server2 to server 3. (Let's server3 has the same schema / database)

    ( expected result: truncate source tables in server1 & sever2 and move/copy source data to serveer3)

    Which command is better among these(MOVE or MERGE or UNION or JOIN COPY)and one I should use?


    My expectation is to move / migrate data from source Db / table to target Db / table by an SQL / PLSQL query / function without any key constraints error. Here same record with same PK/FK exists in server1 & server2.

    By these data, we can able to generate various reports either from server1 or server2. But after moving/merging data to server3, we should have facility to generate reports from server 3 like one generated from server 1/2 .

    How will this happen in new server3? Does the same report generation query will work on server3 now?
    Last edited by nsuresh.india; 09-20-11 at 09:01. Reason: Adding report generation query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •