2

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
  • 2
    Have you tried alter system set session_cached_cursors = 200 scope = spfile;? – Max Vernon Dec 2 '13 at 19:57
4

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.

  • 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

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.