以下适用oracle10g
注意：该sql对表的外键的列数有限制。

column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL (select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1,
cname2,
cname3,
cname4,
cname5,
cname6,
cname7,
cname8)
and i.column_position <= cons.col_cnt group by i.index_name);

以下适用于oracle11g

select case
when ind.index_name is not null then
case
when ind.index_type in ('BITMAP') then
'**Bitmap idx**'
else
'indexed'
end
else
'**Check idx**'
end checker,
ind.index_type,
cons.owner,
cons.table_name,
ind.index_name,
cons.constraint_name,
cons.cols
from (select c.owner,
c.table_name,
c.constraint_name,
listagg(cc.column_name, ',') within group(order by cc.column_name) cols
from dba_constraints c, dba_cons_columns cc
where c.owner = cc.owner
and c.owner = upper('&&schema')
and c.constraint_name = cc.constraint_name
and c.constraint_type = 'R'
group by c.owner, c.table_name, c.constraint_name) cons
left outer join (select table_owner,
table_name,
index_name,
index_type,
cbr,
listagg(column_name, ',') within group(order by column_name) cols
from (select ic.table_owner,
ic.table_name,
ic.index_name,
ic.column_name,
ic.column_position,
i.index_type,
connect_by_root(ic.column_name) cbr
from dba_ind_columns ic, dba_indexes i
where ic.table_owner = upper('&&schema')
and ic.table_owner = i.table_owner
and ic.table_name = i.table_name
and ic.index_name = i.index_name
connect by prior ic.column_position - 1 =
ic.column_position
and prior ic.index_name = ic.index_name)
group by table_owner,
table_name,
index_name,
index_type,
cbr) ind
on cons.cols = ind.cols
and cons.table_name = ind.table_name
and cons.owner = ind.table_owner
order by checker, cons.owner, cons.table_name;