Decode360's Blog

						
								My client has two tables that are identical other then name, I'll call them T1 
and T2.
One synonym, TN that points to T1, while some batch process works on T2.
When the batch is completed the synonym TN is dropped and recreated pointing to 
the T2 table. 
This switch takes place back and forth several times a day causing some unknown 
behavior.

My questions are:
1) Are all objects that reference the synonym TN invalided during the drop 
and recreation? And recompiled once accessed? 
2) If a session is working, running a long query using the TN synonym 
during the drop and recreate, what happens to that session?
3) Can you outline the work flow of what takes place during the process of 
switching the synonym at runtime for both existing and new sessions making 
requests using the TN synonym

I don't like the switching aspect myself, but need to get some facts on the 
process.


						
						
						
				

						
								1) in 9i, if you "create or replace synonym T for T1" and later "create or 
replace synonym T for T2" - all referencing PLSQL is invalidated, all 
referencing views are invalidated, all referencing parsed SQL in the shared 
pool is invalidated.

They will be recompiled automatically upon their next reference

In 10g, all referencing parsed SQL in the shared pool is invalidated - but NOT 
plsql and NOT views.


2) the query should run to completion.  However, if it is a procedure running 
that long running query and the query is static sql and hence the procedure is 
invalid - no one can run it until the procedure is finished running (because no 
one can compile it).

3) see #1.


I would strongly recommend 10g for this switch back and forth - but bear in 
mind that create or replace synonym will invalidate all SQL that references it 
regardless (burst of hard parse everytime you do this) 
						
						
								

						
				

						
								You say that PL/SQL and views in 10G would not be invalidated when the synonym 
was redirected.  Would the view pick up the new table? 
						
						
								

						
				

														
																Yes, the view would "pick up the new table"

We will flip flop from T1 to T2 below:


ops$tkyte@ORA10GR2> create table t1 ( x int );
Table created.

ops$tkyte@ORA10GR2> insert into t1 values ( 1 );
1 row created.

ops$tkyte@ORA10GR2> create or replace synonym t for t1;
Synonym created.

ops$tkyte@ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          for c in ( select * from t )
  5          loop
  6                  dbms_output.put_line( c.x );
  7          end loop;
  8  end;
  9  /
Procedure created.

ops$tkyte@ORA10GR2> create or replace view v as select * from t;
View created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec p
1

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from v;

         X
----------
         1

ops$tkyte@ORA10GR2> select object_name, status from user_objects where 
object_name in ( 'P', 'V' );

OBJECT_NAME                    STATUS
------------------------------ -------
P                              VALID
V                              VALID

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t2 ( x int );

Table created.

ops$tkyte@ORA10GR2> insert into t2 values ( 2 );
1 row created.

ops$tkyte@ORA10GR2> create or replace synonym t for t2;
Synonym created.

ops$tkyte@ORA10GR2> select object_name, status from user_objects where 
object_name in ( 'P', 'V' );

OBJECT_NAME                    STATUS
------------------------------ -------
P                              VALID?   --9i為INVALID?
V                              VALID?   --9i為INVALID?

ops$tkyte@ORA10GR2> exec p
2

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from v;

         X
----------
         2