It is more a restriction on LOG ERRORS in that we need to log errors in the database that is the target for the merge. So lets build this up
DB19S (remote database)
SQL> create table remote as
2 select object_id,
3 owner,
4 object_name
5 from dba_objects
6 where mod(object_id,10)!=0;
Table created.
SQL>
SQL> alter table remote
2 add constraint remote_chk check ( mod(object_id,10)!=0 );
Table altered.
DB19 (source database)
SQL> create table source as
2 select object_id,
3 owner,
4 object_name
5 from dba_objects
6 where object_id is not null;
Table created.
Now we'll ensure a merge without errors works ok
SQL> merge into remote@db19s r
2 using ( select * from source
3 where mod(object_id,10)!=0
4 ) s
5 on ( r.object_id = s.object_id )
6 when matched
7 then
8 update set r.object_name = s.object_name
9 when not matched
10 then
11 insert (r.object_id,r.owner,r.object_name)
12 values (s.object_id,s.owner,s.object_name);
74020 rows merged.
and then see where things go wrong :-)
SQL> roll;
Rollback complete.
SQL> merge into remote@db19s r
2 using ( select * from source
3 ) s
4 on ( r.object_id = s.object_id )
5 when matched
6 then
7 update set r.object_name = s.object_name
8 when not matched
9 then
10 insert (r.object_id,r.owner,r.object_name)
11 values (s.object_id,s.owner,s.object_name);
merge into remote@db19s r
*
ERROR at line 1:
ORA-02290: check constraint (MCDONAC.REMOTE_CHK) violated
ORA-02063: preceding line from DB19S
Now I'll try craft a *local* error table ...and we'll see that will not work
SQL> create table err$_remote (
2 ORA_ERR_NUMBER$ NUMBER
3 , ORA_ERR_MESG$ VARCHAR2(2000)
4 , ORA_ERR_ROWID$ ROWID
5 , ORA_ERR_OPTYP$ VARCHAR2(2)
6 , ORA_ERR_TAG$ VARCHAR2(2000)
7 , OBJECT_ID VARCHAR2(4000)
8 , OWNER VARCHAR2(32767)
9 , OBJECT_NAME VARCHAR2(32767)
10 );
Table created.
SQL>
SQL> merge into remote@db19s r
2 using ( select * from source
3 ) s
4 on ( r.object_id = s.object_id )
5 when matched
6 then
7 update set r.object_name = s.object_name
8 when not matched
9 then
10 insert (r.object_id,r.owner,r.object_name)
11 values (s.object_id,s.owner,s.object_name)
12 log errors into err$_remote reject limit unlimited;
merge into remote@db19s r
*
ERROR at line 1:
ORA-02290: check constraint (MCDONAC.REMOTE_CHK) violated
ORA-02063: preceding line from DB19S
So then I'll it with a remote error logging table
SQL> merge into remote@db19s r
2 using ( select * from source
3 ) s
4 on ( r.object_id = s.object_id )
5 when matched
6 then
7 update set r.object_name = s.object_name
8 when not matched
9 then
10 insert (r.object_id,r.owner,r.object_name)
11 values (s.object_id,s.owner,s.object_name)
12 log errors into err$_remote@db19s reject limit unlimited;
log errors into err$_remote@db19s reject limit unlimited
*
ERROR at line 12:
ORA-38919: remote table not supported for DML error logging
Thus the way you'll need to use to get around this is for your merge to PULL not PUSH...
DB19S (remote database)
SQL> exec dbms_errlog.create_error_log('REMOTE');
PL/SQL procedure successfully completed.
SQL> create or replace
2 procedure do_it_over_there is
3 begin
4 merge into remote r -- LOCAL TARGET
5 using ( select * from source@db19 -- REMOTE SOURCE
6 ) s
7 on ( r.object_id = s.object_id )
8 when matched
9 then
10 update set r.object_name = s.object_name
11 when not matched
12 then
13 insert (r.object_id,r.owner,r.object_name)
14 values (s.object_id,s.owner,s.object_name)
15 log errors reject limit unlimited;
16 end;
17 /
Procedure created.
DB19 (source)
SQL> exec do_it_over_there@db19s
PL/SQL procedure successfully completed.
SQL> select count(*) from err$_remote@db19s;
COUNT(*)
----------
8228