Required indexes in a schema

column status format a7
column table_name format a30
column columns format a40 word_wrapped

select decode(indexes.table_name,null,'missing','ok') status,
constraints.table_name,
constraints.columns
from
(select table_name,
constraint_name,
join(cursor
(
select column_name
from user_cons_columns
where constraint_name = user_constraints.constraint_name
)) columns
from user_constraints
where constraint_type = 'R'
) constraints,
(select table_name, index_name,
join(cursor
(
select column_name
from user_ind_columns
where index_name = user_indexes.index_name
)) columns
from user_indexes) indexes
where constraints.table_name = indexes.table_name (+)
and constraints.columns = indexes.columns (+);