Oracle 18c

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_pk2 PRIMARY KEY (id, value),
    CONSTRAINT parent_child_fk
        FOREIGN KEY (id) REFERENCES parent(id)
                ON DELETE CASCADE
);
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)
                ON DELETE CASCADE
);
WITH f AS
(
    SELECT
        constraint_name, table_name, r_constraint_name
    FROM
        user_constraints
    WHERE
        constraint_type = 'R'
), p AS 
(
    SELECT
        constraint_name, table_name
    FROM
        user_constraints
    WHERE
        constraint_type = 'P'
),j (child_table, f_key, column_name, parent_table, p_key) AS 
(
    SELECT f.table_name, f.constraint_name, c.column_name, p.table_name, f.r_constraint_name
      FROM p
      JOIN f
        ON p.constraint_name = f.r_constraint_name
      LEFT JOIN user_cons_columns c
        ON c.constraint_name = f.constraint_name
    UNION ALL
    SELECT
        'PARENT', 
        (SELECT constraint_name 
         FROM p
         WHERE table_name = 'PARENT'), NULL, NULL, NULL
    FROM dual
)
SELECT
    MAX(level) AS lvl, child_table, f_key, 
    LISTAGG(column_name,',') WITHIN GROUP (ORDER BY column_name) AS f_key, 
    parent_table, p_key
FROM
    j
START WITH parent_table IS NULL
CONNECT BY nocycle parent_table = prior child_table
GROUP BY child_table, f_key, parent_table, p_key
ORDER BY lvl, parent_table, child_table


LVL
CHILD_TABLE
F_KEY
F_KEY
PARENT_TABLE
P_KEY


1
PARENT
PARENT_PK
null
null
null

2
CHILD
PARENT_CHILD_FK
ID
PARENT
PARENT_PK

3
GRANDCHILD
CHILD_GRANDCHILD_FK
ID,VALUE
CHILD
CHILD_PK2

LVL	CHILD_TABLE	F_KEY	F_KEY	PARENT_TABLE	P_KEY
1	PARENT	PARENT_PK	null	null	null
2	CHILD	PARENT_CHILD_FK	ID	PARENT	PARENT_PK
3	GRANDCHILD	CHILD_GRANDCHILD_FK	ID,VALUE	CHILD	CHILD_PK2