Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table parent (
  id NUMBER(10),
  value      varchar2(30),
constraint parent_pk primary key (id)
);
CREATE TABLE child
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint child_pk primary key (id,value),
CONSTRAINT parent_child_fk
FOREIGN KEY (id)
REFERENCES parent(id));
CREATE TABLE grandchild
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint grandchild_pk primary key (id,value),
CONSTRAINT child_grandchild_fk
FOREIGN KEY (id,value)
REFERENCES child(id,value));
select distinct table_name, constraint_name, column_name, r_table_name, position, constraint_type 
from (
    SELECT uc.table_name, 
    uc.constraint_name, 
    cols.column_name, 
    (select table_name from user_constraints where constraint_name = uc.r_constraint_name) 
        r_table_name,
    (select column_name from user_cons_columns where constraint_name = uc.r_constraint_name and position = cols.position) 
        r_column_name,
    cols.position,
    uc.constraint_type
    FROM user_constraints uc
    inner join user_cons_columns cols on uc.constraint_name = cols.constraint_name 
    where constraint_type != 'C'
) 
start with table_name = 'PARENT' and column_name = 'ID'  
connect by nocycle 
prior table_name = r_table_name 
and prior column_name = r_column_name;


TABLE_NAME
CONSTRAINT_NAME
COLUMN_NAME
R_TABLE_NAME
POSITION
CONSTRAINT_TYPE


PARENT
PARENT_PK
ID
null
1
P

GRANDCHILD
CHILD_GRANDCHILD_FK
ID
CHILD
1
R

CHILD
PARENT_CHILD_FK
ID
PARENT
1
R


SELECT 'ALTER TABLE "' || u.owner || '"."' || u.table_name || '" '
       || 'DISABLE CONSTRAINT "' || u.constraint_name || '"' AS statement
FROM   user_constraints u
       INNER JOIN user_constraints r
       ON (   u.constraint_type = 'R'
          AND r.constraint_type IN ('P', 'U')
          AND u.r_owner = r.owner
          AND u.r_constraint_name = r.constraint_name)
START WITH r.table_name       = 'PARENT'
CONNECT BY PRIOR u.owner      = r.owner
AND        PRIOR u.table_name = r.table_name;


STATEMENT


ALTER TABLE "FIDDLE_BGGLESDPTJARZAFCGWMU"."CHILD" DISABLE CONSTRAINT "PARENT_CHILD_FK"

ALTER TABLE "FIDDLE_BGGLESDPTJARZAFCGWMU"."GRANDCHILD" DISABLE CONSTRAINT "CHILD_GRANDCHILD_FK"

TABLE_NAME	CONSTRAINT_NAME	COLUMN_NAME	R_TABLE_NAME	POSITION	CONSTRAINT_TYPE
PARENT	PARENT_PK	ID	null	1	P
GRANDCHILD	CHILD_GRANDCHILD_FK	ID	CHILD	1	R
CHILD	PARENT_CHILD_FK	ID	PARENT	1	R