Finding related tables recursively

When we try to refresh a set of tables from one database to another database, we have to refresh all direct/indirect parent/child tables to those set of tables for data integrity. To find out all those related tables, here is the query.

select

    distinct related_table

from

    ( /* All related tables in the database */

      ( /* Related tables are parent tables in this subquery */

       select

              parent_con.owner       related_table_owner,

              parent_con.table_name  related_table,

              child_con.owner        given_table_owner,

              child_con.table_name   given_table

       from

              all_constraints  parent_con,

              all_constraints  child_con

       where

              child_con.r_constraint_name = parent_con.constraint_name

      )

      union

      ( /* Related tables are child tables in this subquery */

       select

              child_con.owner        related_table_owner,

              child_con.table_name   related_table,

              parent_con.owner       given_table_owner,

              parent_con.table_name  given_table

       from

              all_constraints  parent_con,

              all_constraints  child_con

       where

              child_con.r_constraint_name = parent_con.constraint_name

      )

    )

connect by nocycle prior related_table = given_table

start with

        given_table_owner = 'MURTY'

    and given_table in ('TAB1', 'TAB2')

order by 1;