Re: alter session set cursor_sharing=force not work in 11.2.0.1

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



      
--
http://www.freelists.org/webpage/oracle-l


Other related posts: