|
me,
37 ic.column_name,
38 ic.column_position,
39 i.index_type,
40 connect_by_root(ic.column_name) cbr
41 from dba_ind_columns ic, dba_indexes i
42 where ic.table_owner = upper('&&schema')
43 and ic.table_owner = i.table_owner
44 and ic.table_name = i.table_name
45 and ic.index_name = i.index_name
46 connect by prior ic.column_position - 1 =
47 ic.column_position
48 and prior ic.index_name = ic.index_name)
49 group by table_owner,
50 table_name,
51 index_name,
52 index_type,
53 cbr) ind
54 on cons.cols = ind.cols
55 and cons.table_name = ind.table_name
56 and cons.owner = ind.table_owner
57 order by checker, cons.owner, cons.table_name;
CHECKER INDEX_TYPE OWNER TABLE_NAME INDEX_NAME CONSTRAINT_NAME COLS
------------- ---------- ------------------------- ------------------------------ -------------------- ------------------------------ ------------------------------
**Check idx** AMY EMP EMP_FK DEPTNO
SQL> SQL>
|