delete

Show foreign keys without index

Oracle 9 does not create automaticaly index for foreign key. Therefore use this select to find such cases: select /*+NO_MERGE*/c.TABLE_NAME, c.CONSTRAINT_NAME, col.COLUMN_NAME from all_constraints c, all_cons_columns col where c.owner = 'RMS' and c.constraint_name not like 'SYS_%' and c.constraint_type = 'R' and c.constraint_name = col.constraint_name and not exists (select i_col.COLUMN_NAME from all_indexes i, all_ind_columns i_col where i.table_name = c.TABLE_NAME and i.owner = c.OWNER and i_col.index_nameĀ  = i.INDEX_NAME and i_col.COLUMN_NAME = col.COLUMN_NAME) order...