Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
同义词切换对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

														
posted on 2009-03-24 20:38 decode360 阅读(395) 评论(0)  编辑  收藏 所属分类: 07.Oracle

只有注册用户登录后才能发表评论。


网站导航: