4 Replies Latest reply: Feb 6, 2010 8:21 AM by 750943 RSS

    Merge Table Issue

    750943
      We are trying to merge a table from child workspace into LIVE workspace, it is running for more than 72 hours now and its still merging it. Our problem is, it looks like it locked the LIVE workspace so we could not do update in the LIVE workspace, we were thinking mergetable only locks that particular table and other will be available for update. Is our understanding wrong? what will happen to LIVE workspace when we execute DBMS_WM.mergetable(?,?), command, any help will be greatly appreciated, Thanks.

      -Naga
        • 1. Re: Merge Table Issue
          Ben Speckhard-Oracle
          Hi,

          When dbms_wm.MergeTable is run it will acquire a shared lock on both the parent workspace(LIVE) as well as the workspace being merged. This will allow other merge tables to be executed as well as DMLs in any workspace for the same table. We do lock the individual rows being merged, so there is the potential for a block to occur if another session is updating the same row(s) either by another mergeTable or dml.

          Regards,
          Ben
          • 2. Re: Merge Table Issue
            750943
            Ben,
            Thanks for your information but just for me to be clear, so you are saying we still can do update operation on other tables and this table as long as that rows does not affect the merge process, please let me know if my understanding is not correct?

            I am asking this because, in our case, it looks like all the versioned tables are blocked from update that causing our PROD environment is down for 3 days now, please help, Thanks.
            • 3. Re: Merge Table Issue
              Ben Speckhard-Oracle
              Hi,

              Yes, your understanding is accurate.

              Regards,
              Ben
              • 4. Re: Merge Table Issue
                750943
                Thanks a lot Ben, I think I was on the call you had on Thurday with BCBSM, Thanks.