DDL Against An Object Dependent On By A Synonym Invalidates The Synonym Object. (Doc ID 603093.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.

Symptoms

If a DDL occurs against a object dependent on by a synonym and the DDL operation results in the synonym object 
(and other objects dependent on the synonym) becoming invalid.
Test Case:
SQL> CREATE TABLE tab1(x NUMBER) 
  2     PARTITION BY RANGE (x) (
  3     PARTITION p1 VALUES LESS THAN (10)
  4     , PARTITION pM VALUES LESS THAN (MAXVALUE)
  5     );

Table created.


SQL> CREATE OR REPLACE SYNONYM syn1 FOR tab1;
Synonym created.
 

SQL> CREATE PACKAGE p
  2     AS
  3     PROCEDURE p (
  4     i_param                        IN syn1.x%TYPE
  5     );
  6     END p;
  7  /

Package created.


SQL> ALTER TABLE tab1 SPLIT PARTITION p1 AT (5) INTO (PARTITION p2, PARTITION p1
);

Table altered.



SQL> ALTER TABLE tab1 DROP PARTITION p2;

Table altered.


SQL> SELECT object_name, object_type, status FROM user_objects WHERE object_name

  2  IN ('SYN1', 'P');

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS
------------------- -------
P
PACKAGE             INVALID

SYN1
SYNONYM             INVALID

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