update of partition key on reference partitioned table results in ORA 02292 (4274186)

Chat now with support

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

update of partition key on reference partitioned table results in ORA 02292
Description

Parent and child table are reference partitioned using a FK constraint. Row movement is enabled on both tables.

When an existing record in the parent table is being updated, and the partition key is changed such that the row must move to a new partition. Target gets ORA-02292: integrity constraint (CONSTRAINT_NAME) violated - child record found.
Cause

When an update occurs on parent table resulting in row movement change (moving from one partition to another), oracle executes in background with delete, insert, then update for this row. When delete occurs on the parent row with existing child record, it raises ORA-02292.
Resolution

WORKAROUND:

Upgrade SharePlex to 7.6.2.59 or above. SharePlex post process will use oracle hint RESTRICT_ALL_REF_CONS to suppress ORA-02922 for delete on parent row to allow transaction to complete.

Row movement should be enabled on both parent and child tables on both source and target servers.

STATUS:

This issue is fixed in SharePlex 7.6.2.59.
Defect ID

ST100767

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title