Hello Gurus,
we have an issue in our organization, while performing some maintenance our DBA messed up sequences and now we need to find all the sequences and the table primary key column that these are being used. and see I sequence need to be incremented or not, I got some success with the following script where primary key column matches exactly that of sequence name, but is there any script or any way I can find primary key columns and sequences that do not match. my last option would be to search in the application code.
SELECT ac.owner, ac.TABLE_NAME,acc.COLUMN_NAME, ds.SEQUENCE_NAME , ds.LAST_NUMBER,acc.POSITION,ac.CONSTRAINT_NAME
FROM All_Cons_Columns acc , All_Constraints ac, Dba_Sequences ds
WHERE ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
AND ac.CONSTRAINT_TYPE ='P' --AND acc.POSITION =1
AND ds.SEQUENCE_OWNER = ac.OWNER
AND ds.SEQUENCE_NAME = acc.COLUMN_NAME
AND ac.OWNER = acc.OWNER
ORDER BY ac.TABLE_NAME, acc.position
;