SQL> select ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE
  2  from v$parameter
  3  where name='open_cursors';
ISSES ISSYS_MOD ISINS
----- --------- -----
FALSE IMMEDIATE TRUE

ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

The change takes effect in subsequent sessions
but here is immediate for open cursors

hi blackswan

SSYS_MODIFIABLE immediate for open cursors implies session will have new value of open cursor

and your statement imply session will have old value of open cursor

what is true

i got error from old session even after increasing open cursors para

but want to understand how to avoid if know particular session open cursor value will exceed,increasing para value will not help as per you

Hi Blackswan,


I got your point,what i want to know is if we see some sessions about to exceed ,meaning sessions will trhough error fail

What is fix in this situation (killing that session not solution as it might be doing important task),as you mentioned increasing open cursors parameter value will not help

Thanks

Hi Blackswan,


I got your point,what i want to know is if we see some sessions about to exceed ,meaning sessions will trhough error fail

What is fix in this situation (killing that session not solution as it might be doing important task),as you mentioned increasing open cursors parameter value will not help

Thanks

Hi Blackswan,

Yes i know fixing code is permanent fix,but it can take some time,for identifying and fixing it (as per process)
if we see this issue as DBA like to do some workaround instead of just waiting for app team to fix the code and have outage

there has to be some way to fix this on the fly

also if you explain from sessions perspective ,how session get this value ,from where (from sga,memory,any file) will be helpful in understanding it better

thanks

hi blackswan
what you say about below sql

alter system set open_cursors = 400 scope=both;

(assume using spile)wii it change open_cursors to 400 in current session or no

if no please share link of oracle doc for ref.

thanks

select name,value,ISSYS_MODIFIABLE from v$parameter where name='open_cursors';
NAME, VALUE , ISSYS_MOD
---------------------------------------------------
open_cursors 300 IMMEDIATE


If it is a dynamic parameter #
sql> alter system set open_cursors=500 scope=both;
scope=both
# Value is updated to spfile
# Value is updated in memory

>>Value is updated in memory ---what it means current sessions will get changed value or no

Blackswan - have you actually read the quote from the documentation Michel posted in the 4th post in this thread?
It explicitly states that modifying open_cursors affects existing sessions.

querying views like v$open_cursors
sesstat,sessions

Hi Edstevens
cmon you cannot be so naive,

else i have to setup env for testing (it is time consuming )