Invalid synonyms

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:

SQL> create procedure p1
  2  is
  3  begin
  4    null;
  5  end p1;
  6  /

Procedure is aangemaakt.

SQL> create procedure p2
  2  is
  3  begin
  4    p1;
  5  end p2;
  6  /

Procedure is aangemaakt.

SQL> create synonym p2_syn for p2
  2  /

Synoniem is aangemaakt.

SQL> create synonym p1_syn for p1
  2  /

Synoniem is aangemaakt.

SQL> select object_name,status from user_objects
  2  where object_name in ('P1','P2','P1_SYN','P2_SYN')
  3  /

OBJECT_NAME                    STATUS
------------------------------ -------
P1                             VALID
P1_SYN                         VALID
P2                             VALID
P2_SYN                         VALID

4 rijen zijn geselecteerd.

SQL> create or replace procedure p1 (p_tekst in varchar2)
  2  is
  3  begin
  4    dbms_output.put_line(p_tekst);
  5  end p1;
  6  /

Procedure is aangemaakt.

SQL> select object_name,status from user_objects
  2  where object_name in ('P1','P2','P1_SYN','P2_SYN')
  3  /

OBJECT_NAME                    STATUS
------------------------------ -------
P1                             VALID
P1_SYN                         VALID
P2                             INVALID
P2_SYN                         VALID

4 rijen zijn geselecteerd.

This was what I expected. The synonym always remains valid as it really doesn't have a status.

This is the same test in our new 10.2.0.3 database:

SQL> create procedure p1
  2  is
  3  begin
  4    null;
  5  end p1;
  6  /

Procedure is aangemaakt.

SQL> create procedure p2
  2  is
  3  begin
  4    p1;
  5  end p2;
  6  /

Procedure is aangemaakt.

SQL> create synonym p2_syn for p2
  2  /

Synoniem is aangemaakt.

SQL> create synonym p1_syn for p1
  2  /

Synoniem is aangemaakt.

SQL> select object_name,status from user_objects
  2  where object_name in ('P1','P2','P1_SYN','P2_SYN')
  3  /

OBJECT_NAME                    STATUS
------------------------------ -------
P1                             VALID
P1_SYN                         VALID
P2                             VALID
P2_SYN                         VALID

4 rijen zijn geselecteerd.

SQL> create or replace procedure p1 (p_tekst in varchar2)
  2  is
  3  begin
  4    dbms_output.put_line(p_tekst);
  5  end p1;
  6  /

Procedure is aangemaakt.

SQL> select object_name,status from user_objects
  2  where object_name in ('P1','P2','P1_SYN','P2_SYN')
  3  /

OBJECT_NAME                    STATUS
------------------------------ -------
P1                             VALID
P1_SYN                         INVALID
P2                             INVALID
P2_SYN                         INVALID

4 rijen zijn geselecteerd.

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.