-
Title
Script for disabling ON DELETE CASCADE constraints on target -
Description
The guideline for establishing one way replication (also termed as replication for reporting) states that you may want to disable ON DELETE CASCADE constraints on the target. You may want to refer to Shareplex Admin Guide for further details. If there are numerous tables that have such constraints enabled on target, then a script may come in handy.
-
Cause
Need to disable ON DELETE CASCADE constraints on the target.
-
Resolution
The following script can be used to disable ON DELETE CASCADE constraints on the target:
Disable ON DELETE CASCADE constraints on the target system. You can can the following script to disable them.
SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';'
FROM dba_constraints
WHERE constraint_type = 'R'
And delete rule="CASCADE"
AND owner NOT IN ('SYSTEM', 'SYS')
/
It is advisable to test it first in a QA environment before deploying it in production environment.