This discussion is archived
10 Replies Latest reply: Dec 4, 2012 7:38 PM by madamovi RSS

How change "sessions" parameter when spfile?

user8768362 Newbie
Currently Being Moderated
Hi,
I am running a 10g XE database, which is spfile based.

How can I change the "sessions" parameter?

According to the documentation it is a non-modifiable parameter, which that it cannot be set through ALTER SYSTEM or ALTER SESSION commands. So how should I change it. Should I manipulate the init*.ora file and restart the DB? Will then the init*.ora file be reread?

Is there a way to create a text-init.ora file out of a sp-file?

Thanks in advance
  • 1. Re: How change "sessions" parameter when spfile?
    Aman.... Oracle ACE
    Currently Being Moderated
    I am not a user of XE but I believe, the command should work.
    alter system set sessions=whatever_number_youwant scope=spfile;
    After this give a restart and see.

    If this doesn't work, create the pfile using the command,
    create pfile from spfile;
    Do the changes and than restart the db using the pfile.

    HTH
    Aman....
  • 2. Re: How change "sessions" parameter when spfile?
    sybrand_b Guru
    Currently Being Moderated
    The sessions parameter is derived from the processes parameter.
    This parameter is static.
    How to change static parameters?

    alter system set <parameter>=<value> scope=spfile;

    shutdown immediate
    startup

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: How change "sessions" parameter when spfile?
    user8768362 Newbie
    Currently Being Moderated
    Great, thanks,

    my problem was, i was trying scope=both. So non-modifiable is related to "scope" and not to the fact, if the parameter is modifiable per ALTER SYSTEM or not.
  • 4. Re: How change "sessions" parameter when spfile?
    EdStevens Guru
    Currently Being Moderated
    user8768362 wrote:
    Great, thanks,

    my problem was, i was trying scope=both. So non-modifiable is related to "scope" and not to the fact, if the parameter is modifiable per ALTER SYSTEM or not.
    From that sense, all parameters are modifiable. If they weren't, why would they even be accessible, why would documentation talk about them and their range of values? Yes, the term 'modifiable' in this context means 'dynamically modifiable' or 'modifiable without restart'. Typically, 'non-modifiable' parameters are those that require adjustments to fundamental memory allocation/management and thus require a restart in order to re-allocate that memory.
  • 5. Re: How change "sessions" parameter when spfile?
    599268 Newbie
    Currently Being Moderated
    I have done the same routine :

    - changed sessions from 170 to 115 in splfile ( thru 10g webconsole )
    - restarted DB ... but still is the current is : 170 where as spfile : 115
    - restarted the machine and os
    - all over again .. same
    - tried a diff target # like 170 --- > 20 ... no diff
    - checked : "SELECT name,value FROM v$parameter WHERE name = 'spfile';" made sure stick on the right spfile
    which was correct : "C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA"
    - restarted DB from command prompt ... no diff
    -checked "Select name,value from v$parameter where name like '%sessions%'" >>> 170

    any idea what could be the issue and how can I change this parameter to 115 ?

    thanks!
  • 6. Re: How change "sessions" parameter when spfile?
    ajallen Pro
    Currently Being Moderated
    SESSIONS = PROCESSES * 1.1 + 5, so instead of setting SESSIONS = 115, try setting PROCESSES=100 and bouncing the database and see what happens.

    Oracle uses this default setting (PROCESSES*1.1+5) as the minimum, so if you have PROCESSES = 150, you will not be able to set SESSIONS < 170.
  • 7. Re: How change "sessions" parameter when spfile?
    931126 Newbie
    Currently Being Moderated
    Hi there,

    I have an issue related to all this parameter changes stuff.

    I need to change two NLS parameters for all session. I've tested them first with ALTER SESSION and it worked fine, so I've applied the same change with ALTER SYSTEM...SCOPE=SPFILE and restarted the DB.

    Strange thing is that when connecting to the DB, I can see that the changes has been applied in the instance parameter (select * from NLS_INSTANCE_PARAMETERS;) but the session parameters are still the old ones (select * from NLS_SESSION_PARAMETERS;). How is it possible? Aren't the session parameters supposed to be the same than instance paramaters, at least until you issue an ALTER SESSION? What can prevent the session to set the parameter with the instance values?
  • 8. Re: How change "sessions" parameter when spfile?
    madamovi Newbie
    Currently Being Moderated
    Strange thing is that when connecting to the DB, I can see that the changes has been applied in the instance parameter (select * from NLS_INSTANCE_PARAMETERS;) but the session parameters are still the old ones (select * from NLS_SESSION_PARAMETERS;). How is it possible? Aren't the session parameters supposed to be the same than instance paramaters, at least until you issue an ALTER SESSION? What can prevent the session to set the parameter with the instance values?
    I have the exactly same issue. Further, SELECT * FROM v$parameter p WHERE name LIKE 'nls%'; shows the old values. When I open SPFILE in a text editor, I see new values.

    Any ideas what could cause this and how to fix it?
  • 9. Re: How change "sessions" parameter when spfile?
    sb92075 Guru
    Currently Being Moderated
    madamovi wrote:
    Strange thing is that when connecting to the DB, I can see that the changes has been applied in the instance parameter (select * from NLS_INSTANCE_PARAMETERS;) but the session parameters are still the old ones (select * from NLS_SESSION_PARAMETERS;). How is it possible? Aren't the session parameters supposed to be the same than instance paramaters, at least until you issue an ALTER SESSION? What can prevent the session to set the parameter with the instance values?
    I have the exactly same issue. Further, SELECT * FROM v$parameter p WHERE name LIKE 'nls%'; shows the old values. When I open SPFILE in a text editor, I see new values.

    Any ideas what could cause this and how to fix it?
    start your own thread!


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
    When I open SPFILE in a text editor, I see new values.
    when you open spfile in text editor, your corrupt it & render it useless.
    has DB been started using the spfile?
  • 10. Re: How change "sessions" parameter when spfile?
    madamovi Newbie
    Currently Being Moderated
    sb92075 wrote:
    madamovi wrote:
    Strange thing is that when connecting to the DB, I can see that the changes has been applied in the instance parameter (select * from NLS_INSTANCE_PARAMETERS;) but the session parameters are still the old ones (select * from NLS_SESSION_PARAMETERS;). How is it possible? Aren't the session parameters supposed to be the same than instance paramaters, at least until you issue an ALTER SESSION? What can prevent the session to set the parameter with the instance values?
    I have the exactly same issue. Further, SELECT * FROM v$parameter p WHERE name LIKE 'nls%'; shows the old values. When I open SPFILE in a text editor, I see new values.

    Any ideas what could cause this and how to fix it?
    start your own thread!


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
    When I open SPFILE in a text editor, I see new values.
    when you open spfile in text editor, your corrupt it & render it useless.
    has DB been started using the spfile?
    I didn't change the SPFILE, just viewed it. I assume that the DB has been started with SPFILE. I just installed XE 11g and I use it as is. I just changed couple of NLS parameters using ALTER SYSTEM. The changes showed in SPFILE but it seems that they are not picked up (after restaring DB), as explained above.

    Edited by: madamovi on 4-Dec-2012 7:38 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points