How to change SESSION_CACHED_CURSORS for all sessions

Ask Question

The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.

You can change the value for all other sessions, e.g as below:

begin
for c in (select sid, serial# from v$session) loop
   dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/