remidian.com

In Oracle 10G synonyms are marked invalid when you perform DDL on the referenced object. Luckily the synonym is marked valid again when you simply touch the invalidated synonym. The status of the synonym becomes valid again and the DBA_OBJECTS.LAST_DDL_TIME and DBA_OBJECTS.TIMESTAMP columns reflect the time the synonym was validated again. Listing below shows an example for a create or replace of a view with an associated synonym.

[REMI@DB01.REMIDIAN.COM]
SQL> create view v_demo as select * from dual;
 
View created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> create synonym s_demo for remi.v_demo;
 
Synonym created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select object_name, object_type, status
  2    from all_objects
  3   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME                      OBJECT_TYPE    STATUS
-------------------------------- -------------- ---------------
S_DEMO                           SYNONYM        VALID
V_DEMO                           VIEW           VALID
 
[REMI@DB01.REMIDIAN.COM]
SQL> create or replace view v_demo as select * from dual;
 
View created.
 
[REMI@DB01.REMIDIAN.COM]
SQL> select object_name,
  2         object_type,
  3         status,
  4         to_char( last_ddl_time, 'yyyy-mm-dd:hh24:mi:ss') last_ddl_time,
  5         timestamp
  6    from all_objects
  7   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME  OBJECT_TYPE     STATUS          LAST_DDL_TIME       TIMESTAMP
------------ --------------- --------------- ------------------- -------------------
S_DEMO       SYNONYM         VALID           2006-08-30:22:09:30 2006-08-30:22:09:30
V_DEMO       VIEW            VALID           2006-08-30:22:09:30 2006-08-30:22:09:30

Now ‘touch’ the synonym, oracle compiles the synonym and marks it valid again. (You will recieve no error.)

 
[REMI@DB01.REMIDIAN.COM]
SQL> select * from s_demo where 1 = 2 ;
 
no rows selected
 
[REMI@DB01.REMIDIAN.COM]
SQL> select object_name,
  2         object_type,
  3         status,
  4         to_char( last_ddl_time, 'yyyy-mm-dd:hh24:mi:ss') last_ddl_time,
  5         timestamp
  6    from all_objects
  7   where object_name in ('V_DEMO', 'S_DEMO');
OBJECT_NAME  OBJECT_TYPE     STATUS          LAST_DDL_TIME       TIMESTAMP
------------ --------------- --------------- ------------------- -------------------
S_DEMO       SYNONYM         INVALID         2006-08-30:22:09:35 2006-08-30:22:09:35
V_DEMO       VIEW            VALID           2006-08-30:22:09:30 2006-08-30:22:09:30