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.
Rob, this is also explained in Metalink note 438210.1 which says these invalid synonyms will be validated when they are accessed again.
ReplyDeleteyas, thanks.
ReplyDeleteYes, 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.
In 10.2.0.3
ReplyDeleteCreated 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.
Thanks for the interesting addition, ordba.
ReplyDeleteThis script recompiles all existing invalid PL/SQL modules in a database.
ReplyDelete$ORACLE_HOME/rdbms/admin/utlrp.sql
regards