Pages

Wednesday, February 20, 2008

This morning I read an email discussion about having invalid synonyms in our new Oracle 10.2.0.3 database. I frowned a little, thinking it was probably a project manager mixing up words and concepts picked up by his team members. After all, a synonym cannot be invalid: it is only another word for an object. It's the underlying object that can be invalid or missing. And apparently that's what they meant by "invalid synonyms".

But then a developer colleague who was testing our new environment, wanted me to have a look and showed me invalid synonyms in his SQL Navigator gui. The problem with those tools - I think - is that I don't get to know what query they are executing. So I had to reproduce it myself using SQL*Plus. In our old 9.2.0.7 database:

Apparently, Oracle10 marks the synonyms as invalid. And although the definition of the user_objects view changed a bit to reflect new object types, the part that displays the status column did not change. It's still:

decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),

where o is the alias from sys.obj$. So it is the dependency mechanism in Oracle10 that marks the synonyms as invalid when the underlying object changes or becomes invalid. Note that even P1_SYN became invalid, even though the referring P1 procedure changed to another valid procedure.

The note says that "10g behavior makes more sense because the validation of the synonym occurs WITH altering/dropping the underlying table", but I don't agree, which was my point. I like the 9i behaviour more where only the underlying objects become invalid.

It aligns more with how I think of synonyms: "dumb" objects without a status that allow you to use another name or the same name without a schema name as a prefix for a package/procedure/table/view.

Synonyms having a status are non intuitive to me, but maybe I'll get used to it in the future.