We're here to help!

Constraints

Follow

Constraints on the target database can cause conflicts. Especially foreign key constraints with the "delete cascade" feature. This is because if this constraint exists on the source table, then the parent and child rows will be deleted and both deletes will be replicated to the target database. 

Dbvisit Replicate will first delete the parent record on the target database, the constraint will then delete the child record, and then Dbvisit Replicate wants to delete the child record. This will cause a conflict because the child record will not exist anymore.

Unsupported constraints

To check if there are unsupported constraints on the target database, please run the following query:

 ONLY REQUIRED TO RUN AGAINST THE TARGET DATABASE and only if target is ORACLE. Referential integrity constraint check.

 This query may return results, but it is only applicable to schemas and tables that are being replicated.

Prompt ONLY RUN ON TARGET DATABASE!
column owner format A35
set pagesize 150
set linesize 132
select owner,
constraint_name,
table_name, 
delete_rule
from dba_constraints 
where constraint_type = 'R' 
and delete_rule!='NO ACTION'
and owner not in ('XDB','SYS','SYSTEM','SYSMAN','WMSYS','OLAPSYS','MDSYS','APEX_030200',
'FLOWS_FILES','EXFSYS','APPQOSSYS','CTXSYS','ORDDATA','OUTLN',
'DBSNMP','ORDSYS','DBVREP','APEX_040200','LBACSYS');

This is dynamic script which can be run on the target to disable cascade constraints for the replicated schema(s)

Prompt ONLY RUN ON TARGET DATABASE!

Accept l_schema prompt "Enter Schema :"

column owner format A35
set pagesize 150
set linesize 132
select owner, table_name,constraint_name, delete_rule,status 
FROM dba_constraints
where delete_rule = 'CASCADE'
and owner = upper('&l_schema')
order by owner, table_name
/

Prompt ...
Prompt ...
Prompt ......Building disable_del_cons_r.sql to run next
Prompt ......
REM If any are found Disable delete constraints on Target (only)
SET HEADING OFF 
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
Spool disable_del_cons_r.sql
select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
from dba_constraints
where delete_rule = 'CASCADE'
and owner = upper('&&l_schema');
spool off

SQL> @disable_del_cons_r.sql


Have more questions? Submit a request

Comments