It’s always the time to learn something new.
If you missed to learn something during the day, it doesn’t mean you know everything.
It just means you stop learning.
In this case, I believe if I compile the package, that action won’t invalidate synonyms based on that package/procedure/function, as shown in the following example:


create or replace procedure ptest
as
begin
    Null;
end;

create or replace public synonym ptest for scott.ptest;


select owner, object_name, object_type, status from dba_objects where object_name = 'PTEST' and owner in ('SCOTT', 'PUBLIC');

"OWNER"		"OBJECT_NAME"	"OBJECT_TYPE"	"STATUS"
---------------------------------------------------------
"PUBLIC"	"PTEST"			"SYNONYM"		"VALID"
"SCOTT"		"PTEST"			"PROCEDURE"		"VALID"


alter procedure ptest compile;

select owner, object_name, object_type, status from dba_objects where object_name = 'PTEST' and owner in ('SCOTT', 'PUBLIC');

"OWNER"		"OBJECT_NAME"	"OBJECT_TYPE"	"STATUS"
---------------------------------------------------------
"PUBLIC"	"PTEST"			"SYNONYM"		"VALID"
"SCOTT"		"PTEST"			"PROCEDURE"		"VALID"

As you can see, everything is working as expected.

But when you have complex logic in the package (AQ, many dependencies etc.), you should check if dependent synonym will become invalid.

According to the Metalink Note: DDL Against An Object Dependent On By A Synonym Invalidates The Synonym Object. (Doc ID 603093.1), only 10.2.0.3 version is affected.

Apparently, even with later releases of Oracle databases, in some specific cases, something similar may occur.

Solution is to recompile synonym that becomes invalid as a result of DDL operation on complex package.


alter public synonym synonym_name compile;
--or 
create or replace public synonym synonym_name for scott.synonym_name;


Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.