Useful SQLs

Child tables not having index on foreign key columns

select table_name,column_name from user_cons_columns where constraint_name in (select constraint_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table_name') and constraint_type='R') minus (select table_name,column_name from user_ind_columns);

child tables of a parent table

select table_name from user_constraints where r_constraint_name in (select constraint_name from user_constraints where table_name='table name');