Oracle Database Discussions

Hi, all ! Maybe u guys can help me ...

Our production enviroment ( Windows Oracle 10.2.3.0 ) is having some problems to create a private synonym for a object(One package WITHOUT a package body ).
We have a application (APP) user and a schema user(SCHEMA).Both are in the same instance.
The object (PCK) in the schema user and the application user have the grants to execute the package.


The synonym were created without errors :

( LOGGED as APP )

SQL> create synonym PCK for SCHEMA.PCK;

Synonym created.


SQL> select * from user_synonyms where synonym_name like '%PCK%';

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------
PCK SCHEMA PCK


The synonym where created, but it is invalid.

SQL> select object_name, status,object_type from user_objects where object_name like '%PCK%';


OBJECT_NAME STATUS OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------- -------------------
PCK INVALID SYNONYM


The status for the package and the synonym

SQL> select owner,object_name,object_type,status from dba_objects where object_name like '%PCK%';

OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------------ ------------------- -------
SCHEMA PCK PACKAGE VALID
BBM PCK SYNONYM INVALID

The object referenced by the synonym is valid, but I'LL recompile to see if have any errors :

( LOGGED as SCHEMA )
SQL> alter package PCK compile ;
Package altered.


Trying to use the synonym for the package.
( LOGGED as APP )
SQL> desc PCK;

ERROR:

ORA-04045: errors during recompilation/revalidation of APP.PCK
ORA-00980: synonym translation is no longer valid

( Also LOGGED as APP , referencing the schema object )

SQL> desc SCHEMA.PCK;


PROCEDURE Argument Name Type IN/OUT Default
------------------------------------------------------------------------------------ ------------------------------ ------------------------------------------------------------------------- --------- -------

0 rows selected

( No rows select as the package dont have any procedures declared )

I also tryed to simulate this error in a 10.2.1.0 but I didn't got this errors .

Any help I'LL bd welcome !