This discussion is archived
7 Replies Latest reply: Jan 7, 2013 3:23 AM by Osama_Mustafa RSS

Updating Table

827207 Newbie
Currently Being Moderated
Hi,

I have production and development database in which a table call "CORDYS" is existing in both the database.
Normally, cordys table in production database getting updates on daily basis.
I want to update the developement cordys table also with the production cordys table, data.

Exporting whole table and import is really very much time consuming. As this table have 5,00,000 entry.

Is there is any easy way to do it.

Please help..
  • 1. Re: Updating Table
    asahide Expert
    Currently Being Moderated
    Hi,

    How about follows.

    1. Materialized View
    2. Transportable TableSpace (If you can)
    3. Golden Gate...

    Regards,
  • 2. Re: Updating Table
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Pravin wrote:
    Hi,

    I have production and development database in which a table call "CORDYS" is existing in both the database.
    Normally, cordys table in production database getting updates on daily basis.
    I want to update the developement cordys table also with the production cordys table, data.

    Exporting whole table and import is really very much time consuming. As this table have 5,00,000 entry.

    Is there is any easy way to do it.

    Please help..
    -materialized view
  • 3. Re: Updating Table
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    I want to update the developement cordys table also with the production cordys table, data.
    Do you want to update regularly/immediately?
    Exporting whole table and import is really very much time consuming. As this table have 5,00,000 entry.
    If you want to do it once, or once in a week etc, expdp/exp can easily help you and you can finish whole process within 5 minutes for 500000 records, this is really not huge data.

    You can also create a database link to/from production to/from development and use MERGE statement to merge the data of both tables. See bellow on how to use merge statement.

    http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

    Salman
  • 4. Re: Updating Table
    moreajays Pro
    Currently Being Moderated
    Hi,

    Use trigger , refer below example of insert
    CREATE OR REPLACE TRIGGER Example
    AFTER INSERT ON Emp_tab
    FOR EACH ROW
    BEGIN
      INSERT INTO Emp_tab@<DB_Link>
      VALUES ('x');                 
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO Emp_log
        VALUES ('x');
    END;
    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 5. Re: Updating Table
    JohnWatson Guru
    Currently Being Moderated
    I would use dbms_comparison to compare and converge the tables, just run it as a job whenever you want.

    Edited by: JohnWatson on Jan 7, 2013 9:56 AM
    All the other solutions suggested will have a performance impact on production.
  • 6. Re: Updating Table
    953290 Newbie
    Currently Being Moderated
    I'll use materialized view in development using a materialized view log on the table in production.

    Regards,

    Renaud
  • 7. Re: Updating Table
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Renaud Claeys wrote:
    I'll use materialized view in development using a materialized view log on the table in production.

    Regards,

    Renaud
    Please mark this thread as answered.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points