4

I am using Oracle SQL Developer's database diff tool to compare schemas. (SQL developer version : 3.2.09) It works great on some environments, but in one, I can only see differences in tables - the sequences and indexes are all treated as if they are new and not compared against existing ones.

I've confirmed the indexes exist in the target environment, and that my user account should have permission to see them - I have both "select any dictionary" system privilege and the "select_catalog_role" role. Further, I do see the indexes if I explicitly query "all_indexes" and "all_ind_columns" tables.

Any ideas what permissions I might be missing? Again, this works on another environment.

  • Do the sequences and indexes have the same names between the two instance? – thatjeffsmith Jan 14 '15 at 18:15
1

This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.

SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
FROM ALL_INDEXES
WHERE INDEX_NAME LIKE 'EMP%';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.