Use below query to get child key and foreign key status:
col owner form a10 col FK_column form a15 col table_name form a30 select b.owner, b.table_name child_table,b.status, c.column_name FK_column, b.constraint_name from dba_constraints a, dba_constraints b, dba_cons_columns c where a.owner=b.r_owner and b.owner=c.owner and b.table_name=c.table_name and b.constraint_name=c.constraint_name and a.constraint_name=b.r_constraint_name and b.constraint_type='R' and a.owner='&OWNER' and a.table_name='&TABLE_NAME' and a.CONSTRAINT_TYPE='P'; OWNER CHILD_TABLE STATUS FK_COLUMN CONSTRAINT_NAME ---------- ------------------------------ -------- --------------- ------------------------------ DDR MDT_RM_DEVICE_TRAND DISABLED DEV_TRAND_STATU MDT_RM_DACID_TRAND_STATUS_FK S_ID
Enable constraints:
alter table FDR.MDT_RM_CALL_CLOSEOUT_METER disable constraints MDT_RM_CALL_CLOSEOUT_METER_FK; Table altered.
Find the parent table of child table:
SELECT owner, table_name FROM dba_constraints WHERE constraint_name IN (SELECT r_constraint_name FROM dba_constraints WHERE table_name = 'PUROLATOR_ACCOUNTS' AND constraint_type = 'R');