Find Foreign Keys with no Index

for finding foreign keys with no index you could put Owner and Tablespace name in the script and then view your result:


SELECT tbl.*,
       'CREATE INDEX IDXFK_' || substr(tbl.column_name, 1, 23) || ' ON ' ||
       tbl.table_name || ' ( ' || tbl.column_name ||
       ' ) tablespace main_index ; '

  FROM (SELECT c.owner,
               c.table_name,
               cc.column_name,
               cc.position column_position
     
          FROM DBA_constraints c, DBA_cons_columns cc
         WHERE c.constraint_name = cc.constraint_name
           and c.OWNER = 'Yuor Username'
           AND c.constraint_type = 'R'
           and c.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
          FROM DBA_indexes i, DBA_ind_columns ic
         WHERE i.index_name = ic.index_name) tbl
 ORDER BY table_name;