Check the Foreign key constraint related to tables in Oracle

Taking example of HR schema with DEPARTMENT Table. Department table has relation with Employees and Job_history tables.

SQL> SELECT table_name, constraint_name,r_constraint_name,CONSTRAINT_TYPE FROM user_constraints where r_constraint_name = 'DEPT_ID_PK';

TABLE_NAME      CONSTRAINT_NAME R_CONSTRAINT_NAME
--------------- --------------- --------------------
EMPLOYEES       EMP_DEPT_FK     DEPT_ID_PK
JOB_HISTORY     JHIST_DEPT_FK   DEPT_ID_PK

Check column involved in Foreign key constraints:

SELECT a.table_name, c.column_name
FROM user_constraints a, user_cons_columns c
WHERE a.CONSTRAINT_TYPE = 'R'
AND a.R_CONSTRAINT_NAME = 'DEPT_ID_PK'
AND c.constraint_name = a.constraint_name;

Disable all the constraints related to Department Foreign key

BEGIN
FOR I IN (SELECT table_name, constraint_name,r_constraint_name FROM user_constraint t WHERE r_constraint_name='DEPT_ID_PK') LOOP
EXECUTE IMMEDIATE ' alter table ' || I.table_name || ' disable constraint ' || i.constraint_name;
END LOOP;
END;
/