Re: alter session set cursor_sharing=force not work in 11.2.0.1
- From: Yong Huang <yong321@xxxxxxxxx>
- To: sundog315@xxxxxxxxx
- Date: Wed, 20 Oct 2010 07:32:35 -0700 (PDT)
Like Dion, I can't reproduce, i.e., cursor_sharing=force works fine for me.
SQL> alter session set cursor_sharing = exact;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from v$sql t where lower(t.SQL_TEXT) like 'select count(*)
from t%';
COUNT(*)
------------
0
SQL> declare
2 type numTab is table of pls_integer index by binary_integer;
3 i pls_integer;
4 nCount pls_integer;
5 fld_object_id numTab;
6 begin
7 execute immediate 'alter session set cursor_sharing=force';
8 select object_id bulk collect into fld_object_id from t where
rownum<=100;
9 for i in 1..fld_object_id.count loop
10 execute immediate
11 'select count(*) from t where object_id = '||fld_object_id(i) into
nCount;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> select count(*) from v$sql t where lower(t.SQL_TEXT) like 'select count(*)
from t%';
COUNT(*)
------------
1
SQL> select sql_text from v$sql t where lower(t.SQL_TEXT) like 'select count(*)
from t%';
SQL_TEXT
------------------------------------------------------------------------------------------
select count(*) from t where object_id = :"SYS_B_0"
SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
Cursor_sharing=force will not work in PL/SQL *if* the SQL is indeed in PL/SQL
context
(see Note:285447.1). But after execute immediate, cursor_sharing=force should
work.
Yong Huang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
