unindex 11.2 cross schema

Foreign keys might point to another schema. Here is an unindex query for such situation. My earlier queries unindex and unindex 11.2 were built on top of user_* views. Here the queries are using all_* views. I am not using dba_* views as all_* views are usable for wider audience than just dba privileged. Reminder: you probably do not need all of those, but they might explain you some poor execution times or TM lock waits.

select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.owner
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.owner
       , a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from all_constraints a, all_cons_columns b
     where a.owner = b.owner
       and a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.owner,a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select index_owner
       , table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select index_owner
             , table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from all_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
              and prior index_owner = index_owner
         )
    group by index_owner, table_name, index_name, cr
) i on c.owner = i.index_owner and c.cols = i.cols and c.table_name = i.table_name
order by ok,owner,table_name,constraint_name;