bspeckha wrote:Now I got it, It's helpful to me, Thanks for your sharing! Nice writing.
This is expected behavior. During a merge operation, an exclusive lock is typically acquired on the parent workspace. This can be avoided by setting the system parameter ROW_LEVEL_LOCKING to true. This parameter will not have any affect while merging workspaces that are continually refreshed, or that contain changes to valid time enabled tables. Changing this <font face="tahoma,verdana,sans-serif" size="1" color="#000">parameter</font> will result in a shared lock being acquired instead, allowing a dbms_wm.GotoWorkspace operation to be executed from another session.
However, when committing a workspace by specifying remove_workspace=>true, a more stringent lock is acquired while removing the workspace after the rows have been committed, which will block those other sessions while allowing other workspaces to merge into LIVE, but not execute GotoWorkspace.
So, if you have to do a commit as opposed to not removing the workspace, then it is not going to be currently possibly to prevent the block within the other session.