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.
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.
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.