How to change SESSION_CACHED_CURSORS for all sessions

environment-variablesoraclesession

how Can I do to change SESSION_CACHED_CURSORS for all sessions?

I found on google and oracle website:

Alter Session... but I need to changed it for all the session and not only for one of them.

How can I do it?

UPDATE 1:

SQL> alter session set session_cached_cursors=800 scope=both sid='*';
alter session set session_cached_cursors=800 scope=both sid='*'
                                             *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

Best Answer

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;
/

This however will not have any effect on the sessions opened after this point. For that, you have to use ALTER SYSTEM as above and restart the database.

Related Question