同義詞切換對(duì)Objects的狀態(tài)影響
?
??? 改變Synonym的定義，會(huì)使涉及到的objects的status變成invalid，但是9i跟10g還是有區(qū)別。另外簡(jiǎn)單的object在INVALID之后下一次查詢時(shí)即可自動(dòng)編譯，但也有些會(huì)造成一些影響。具體Oracle定期Recompiling的方法有很多，可以直接google一下，Oracle自帶也有腳本，例如：...\oracle\ora92\rdbms\admin\utlirp.sql
?
??? 摘錄一下ask tom的內(nèi)容
?
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
|