This was tested on Oracle 11.2. Some initialisation parameters cannot be changed while the database is open. If you try to change one of these, you get an ORA-02095:
SQL> alter system set sessions = 30;
alter system set sessions = 30
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
If
you are using a server parameter file, you can make the change there
and bounce the database. But if you are not then this option is not
available to you either:
SQL> l
1 alter system set sessions = 30
2* scope = spfile
SQL> /
alter system set sessions = 30
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
SQL>
You
can start using a server parameter file as follows. This assumes that
the database is a test one and nobody else is using it. Otherwise you
should check with the users first then do a shutdown normal to bounce the
database instead:
SQL> create spfile from pfile;
File created.
SQL> startup force;
ORACLE instance started.
Total System Global Area 417669120 bytes
Fixed Size 2148672 bytes
Variable Size 255858368 bytes
Database Buffers 155189248 bytes
Redo Buffers 4472832 bytes
Database mounted.
Database opened.
SQL>
Then you can make the change which failed above:
SQL> alter system set sessions = 30
2 scope = spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 417669120 bytes
Fixed Size 2148672 bytes
Variable Size 260052672 bytes
Database Buffers 150994944 bytes
Redo Buffers 4472832 bytes
Database mounted.
Database opened.
SQL>
I
will look at server parameter files in more detail in a future post but
I wanted to show you this as it happened to me today. You may wonder
why I set the sessions parameter so low. I did it on purpose to demonstrate ORA-00018. Unfortunately, the change did not work:
SQL> l
1 select value from v$parameter
2* where name = 'sessions'
SQL> /
VALUE
----------
776
SQL>
I checked the Oracle 11.2 documentation on this and it said that the minimum value allowed for the sessions parameter is processes * 1.5 + 22. The processes parameter for this database is set to 500:
SQL> l
1 select value from v$parameter
2* where name = 'processes'
SQL> /
VALUE
----------
500
SQL>