Foreign key indexes come back to bite in Oracle 11g

When I started out doing database design, in Oracle 7, I was told that a foreign key always needed an associated index to help with locking issues. I never really questioned it as I was the new guy and did what I was told by the experienced people. Ever since it has been the standard to do this and things were going along quite well until we got to 11g.
While I was still doing database design and including FK indexes as I went (most of the time) other people doing design were a little less careful and some foreign keys were being created without indexes or were created with indexes that had their columns in a different order to the FK (which means the index is not used).
Oracle 11g seems to be a bit more rigid than previous versions and soon we had production locking problems that were locking up parts of the system in a nasty way.

So I had to find all the foreign key indexes that have columns in a different sequence to the columns in the actual foreign key. Fortunately the naming standards mean that all the foreign keys have the name in the format that ends with FK and all the related indexes have the same name but with '_I' appended to the end.
For example a foreign key might be called DEPT_EMP_FK and the index will be called DEPT_EMP_FK_I.

So to find sequence issues I can use

SELECT dbac.table_name,
dbacc.constraint_name,
dbaic.index_name,
dbacc.column_name,
dbacc.position fk_pos,
dbaic.column_position index_pos
FROM dba_constraints dbac,
dba_cons_columns dbacc,
dba_indexes dbai,
dba_ind_columns dbaic
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
dbacc.constraint_name = dbac.constraint_name AND
dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I' AND
dbaic.index_name = dbai.index_name AND
dbaic.column_name = dbacc.column_name AND
dbaic.column_position != dbacc.position
ORDER BY 1,2,3,4,5,6


and to find foreign keys that don't have any indexes at all I can use

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SIS_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I')
ORDER BY 1,2


But this will bring back some instances that don't need to have a foreign key index. This is because if all the columns in the foreign key are in the primary key and are the first columns in the primary key (and in the correct order) then Oracle will use the primary key index. So any foreign key index will not be used.
So we can refine the query above to cope with finding only those that are not already in the primary key in the correct order.

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I') AND
EXISTS(
SELECT 'x'
FROM dba_constraints dbac1,
dba_cons_columns dbacc1,
dba_constraints dbac2,
dba_cons_columns dbacc2
WHERE dbac1.owner = dbac.owner AND
dbac1.table_name = dbac.table_name AND
dbac1.constraint_name = dbac.constraint_name AND
dbacc1.constraint_name = dbac1.constraint_name AND
dbacc1.owner = dbac1.owner AND
dbac2.owner = dbac1.owner AND
dbac2.table_name = dbac1.table_name AND
dbac2.constraint_type = 'P' AND
dbacc2.constraint_name = dbac2.constraint_name AND
dbacc2.owner = dbac2.owner AND
dbacc2.column_name = dbacc1.column_name AND
dbacc2.position != dbacc1.position)
ORDER BY 1,2