同义词切换对Objects的状态影响
改变Synonym的定义，会使涉及到的objects的status变成invalid，但是9i跟10g还是有区别。另外简单的object在INVALID之后下一次查询时即可自动编译，但也有些会造成一些影响。具体Oracle定期Recompiling的方法有很多，可以直接google一下，Oracle自带也有脚本，例如：...\oracle\ora92\rdbms\admin\utlirp.sql
摘录一下ask tom的内容
January 02, 2006
Jim -- Thanks for the question regarding "Synonym runtime swithing", version 9.2.0
You Asked
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.
and we said...
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)
Review & Followup
Rating: 5
10G clarification January 01, 2006
Reviewer: Brad from Dallas
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?
|
Followup:
|
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
|