You will find a complete test case below.
I have one table with a primary key on column ID and an unique constraint on the column NAME.
I then insert two rows using the continually refreshed workspace 'Test' and merge these rows into workspace 'LIVE'.
Then I go to workspace 'Test' again, delete the two rows and insert them again with different IDs but with the same NAMEs as before.
Now both MergeWorkspace and RefreshWorkspace return with an ORA-20232: unique constraint violated.
Especially the error during RefreshWorkspace is in conflict to the documentation, which states: "This procedure (RefreshWorkspace) is ignored if workspace is a continually refreshed workspace."
SQL>
SQL> Select * From v$version Where banner Like 'Oracle%';
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SQL>
SQL> Create Table emp (
2 id Number Constraint emp_pk Primary Key
3 ,name Varchar2(50) Not Null Constraint emp_uk Unique
4 );
Table created.
SQL>
SQL> Exec dbms_wm.EnableVersioning(table_name => 'EMP');
PL/SQL procedure successfully completed.
SQL> Exec dbms_wm.CreateWorkspace(workspace => 'Test', isrefreshed => True, auto_commit => True);
PL/SQL procedure successfully completed.
SQL> Exec dbms_wm.GotoWorkspace(workspace => 'Test');
PL/SQL procedure successfully completed.
SQL>
SQL> Insert Into emp(id, name) Values(1, 'Smith');
1 row created.
SQL> Insert Into emp(id, name) Values(2, 'Jones');
1 row created.
SQL> Commit;
Commit complete.
SQL>
SQL> Select * From emp;
ID NAME
---------- --------------------------------------------------
1 Smith
2 Jones
SQL>
SQL> Exec dbms_wm.GotoWorkspace(workspace => 'LIVE');
PL/SQL procedure successfully completed.
SQL>
SQL> Select * From emp;
no rows selected
SQL>
SQL> Exec dbms_wm.MergeWorkspace(workspace => 'Test', create_savepoint => False, remove_workspace => False, auto_commit => True);
PL/SQL procedure successfully completed.
SQL>
SQL> Select * From emp;
ID NAME
---------- --------------------------------------------------
1 Smith
2 Jones
SQL>
SQL> Exec dbms_wm.GotoWorkspace(workspace => 'Test');
PL/SQL procedure successfully completed.
SQL>
SQL> Delete emp;
2 rows deleted.
SQL>
SQL> Insert Into emp(id, name) Values(4, 'Smith');
1 row created.
SQL> Insert Into emp(id, name) Values(5, 'Jones');
1 row created.
SQL> Commit;
Commit complete.
SQL>
SQL> Exec dbms_wm.GotoWorkspace(workspace => 'LIVE');
PL/SQL procedure successfully completed.
SQL>
SQL> Select * From emp;
ID NAME
---------- --------------------------------------------------
1 Smith
2 Jones
SQL>
SQL> Exec dbms_wm.MergeWorkspace(workspace => 'Test', create_savepoint => False, remove_workspace => False, auto_commit => True);
BEGIN dbms_wm.MergeWorkspace(workspace => 'Test', create_savepoint => False, remove_workspace => False, auto_commit => True); END;
*
ERROR at line 1:
ORA-20232: unique constraint (ETOP_CONFIGURATION.EMP_UK) violated
ORA-06512: at "WMSYS.LT", line 8666
ORA-06512: at line 1
SQL>
SQL> Exec dbms_wm.RefreshWorkspace(workspace => 'Test', auto_commit => True, copy_data => False);
BEGIN dbms_wm.RefreshWorkspace(workspace => 'Test', auto_commit => True, copy_data => False); END;
*
ERROR at line 1:
ORA-20232: unique constraint (ETOP_CONFIGURATION.EMP_UK) violated
ORA-06512: at "WMSYS.LT", line 10427
ORA-06512: at line 1
SQL>
SQL> Exec dbms_wm.RollbackWorkspace(workspace => 'Test', auto_commit => True);
PL/SQL procedure successfully completed.
SQL>
SQL> Exec dbms_wm.RemoveWorkspace(workspace => 'Test', auto_commit => True);
PL/SQL procedure successfully completed.
SQL> Exec dbms_wm.DisableVersioning(table_name => 'EMP');
PL/SQL procedure successfully completed.
SQL> Drop Table emp;
Table dropped.
SQL>
SQL> Spool Off