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?


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

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:

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;

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.

  • THANKs, it worked with the scope option, but I added sid='*' because I needed it for all the instances
    – jcho360
    Dec 3 '13 at 15:25

Please use

alter system set session_cached_cursors=800 scope=spfile;
  • please always add some explanation, what you are doing and why, so that others can find it easier
    – nbk
    Aug 13 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.