Getting parent and child tables (direct and indirect) of a given table recursively

-- To get all parent tables recursively

select

    parent_owner,

    parent_tab,

    child_owner,

    child_tab,

    level

from

    ( /* Self join of all_constraints to get

    all parent and child tables in database */

      select

              parent_con.owner       parent_owner,

              parent_con.table_name  parent_tab,

              child_con.owner        child_owner,

              child_con.table_name   child_tab

      from

              all_constraints  parent_con,

              all_constraints  child_con

      where

              child_con.r_constraint_name = parent_con.constraint_name

    )

connect by nocycle prior parent_tab = child_tab

start with

        child_owner = 'MURTY'

    and child_tab   = 'TAB1'

order by level;

-- To get all child tables recursively --

select

    parent_owner,

    parent_tab,

    child_owner,

    child_tab,

    level

from

    ( /* Self join of all_constraints to get

    all parent and child tables in database */

      select

              parent_con.owner       parent_owner,

              parent_con.table_name  parent_tab,

              child_con.owner        child_owner,

              child_con.table_name   child_tab

      from

              all_constraints  parent_con,

              all_constraints  child_con

      where

              child_con.r_constraint_name = parent_con.constraint_name

    )

connect by nocycle parent_tab = prior child_tab

start with

        parent_owner = 'MURTY'

    and parent_tab   = 'TAB1'

order by level;

Possible syntaxes to create parent & child relationship between tables

create table parent_table (
 p1 number primary key,
 p2 number
);

create table parent_table (
 p1 number constraint parent_table_pk primary key,
 p2 number
);

create table parent_table (
 p1 number,
 p2 number,
 primary key (p1)
);

create table parent_table (
 p1 number,
 p2 number,
 constraint parent_table_pk primary key (p1)
);

alter table parent_table add primary key(p1);

alter table parent_table add constraint parent_table_pk primary key(p1);

create table child_table (
 c1 number,
 c2 number references parent_table(p1)
);

create table child_table (
 c1 number,
 c2 number constraint child_table_fk references parent_table(p1)
);

create table child_table (
 c1 number,
 c2 number,
 foreign key (c2) references parent_table(p1)
);

create table child_table (
 c1 number,
 c2 number,
 constraint child_table_fk foreign key (c2) references parent_table(p1)
);

alter table child_table add foreign key (c2) references parent_table(p1);

alter table child_table add constraint child_table_fk foreign key (c2) references parent_table(p1);