Synonyms Become Invalid After Upgrading To 10g (Doc ID 438210.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Jul-2012***

Symptoms

After upgrading a database from Oracle 8i/9i to 10g,  altering a table causes its associated public synonym to become invalid.

Version 10g after altering a table:

SQL>SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name = 'FIN_RECALL_HEAD'; 

OWNER    OBJECT NAME     OBJECT TYPE  STATUS 
-------  ---------       -----------  ------- 
PUBLIC   FIN_RECALL_HEAD SYNONYM      INVALID 
RMSPRD80 FIN_RECALL_HEAD TABLE        VALID 


==> Table is valid but synonym is not
==> The synonym will be validated when it is first used as shown below:

SQL> connect system/sys 
Connected.

SQL> select * from FIN_RECALL_HEAD;

No rows selected


SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, status from dba_objects where 
2 object_name = 'FIN_RECALL_HEAD'; 

OWNER    OBJECT NAME      OBJECT TYPE    STATUS 
------   ----------       -------------- ----------------
PUBLIC   FIN_RECALL_HEAD  SYNONYM        VALID 
RMSPRD80 FIN_RECALL_HEAD  TABLE          VALID

Version 9i after altering (in this case dropping) a table:

SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name= 'FIN_RECALL_HEAD';
 

OWNER       OBJECT NAME     OBJECT TYPE STATUS
---------   --------------  ----------- ---------
PUBLIC      FIN_RECALL_HEAD SYNONYM     VALID
RMSPRD80    FIN_RECALL_HEAD TABLE       VALID

SQL> DROP TABLE FIN_RECALL_HEAD;

Table dropped.

SQL> SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name= 'FIN_RECALL_HEAD';

OWNER  OBJECT NAME     OBJECT TYPE     STATUS
------ ----------      --------------  ----------
PUBLIC FIN_RECALL_HEAD SYNONYM         VALID

==> Table was dropped but the synonym remains and is shown as valid.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms