Wednesday, February 20, 2008

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.

6 comments:

  1. Rob, this is also explained in Metalink note 438210.1 which says these invalid synonyms will be validated when they are accessed again.

    ReplyDelete
  2. yas, thanks.

    Yes, I know they will become valid automatically.

    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.

    ReplyDelete
  3. In 10.2.0.3

    Created a SYNONYM with no base object.

    SQL> create synonym synonym_test for scott.synonym_test;

    Synonym created.

    SQL> select owner,object_name,status from dba_objects where object_name = 'SYNONYM_TEST';

    OWNER OBJECT_NAME STATUS
    -------------------- -------------------- -------
    SYSTEM SYNONYM_TEST VALID

    1 row selected.

    SQL> select count(*) from SYNONYM_TEST;
    select count(*) from SYNONYM_TEST
    *
    ERROR at line 1:
    ORA-00980: synonym translation is no longer valid


    SQL> select owner,object_name,status from dba_objects where object_name = 'SYNONYM_TEST';

    OWNER OBJECT_NAME STATUS
    -------------------- -------------------- -------
    SYSTEM SYNONYM_TEST VALID

    1 row selected.

    SQL>

    Status stays VALID.

    ReplyDelete
  4. Thanks for the interesting addition, ordba.

    ReplyDelete
  5. This script recompiles all existing invalid PL/SQL modules in a database.

    $ORACLE_HOME/rdbms/admin/utlrp.sql

    regards

    ReplyDelete
  6. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2016/11/07/stp-loop-guard/

    ReplyDelete