7 Replies Latest reply: Jan 7, 2013 5:23 AM by Osama_Mustafa RSS

    Updating Table

    827207
      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
          Hi,

          How about follows.

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

          Regards,
          • 2. Re: Updating Table
            Osama_Mustafa
            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
              Salman Qureshi
              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
                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
                  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
                    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
                      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.