Setting nls_date_format and ORA-02096
 

SQL> STARTUP PFILE=F:\ORACLE\ORA90\DATABASE\INITJAM.ORA
ORACLE instance started.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL> SHOW USER
USER is "SYS"
SQL> alter system set nls_date_format='Month DD, yyyy' SCOPE=MEMORY;
alter system set nls_date_format='Month DD, yyyy' SCOPE=MEMORY
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL> alter system set nls_date_format='Month DD, yyyy';
alter system set nls_date_format='Month DD, yyyy'
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL> alter system set nls_date_format='Month DD, yyyy' SCOPE=BOTH;
alter system set nls_date_format='Month DD, yyyy' SCOPE=BOTH
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL> alter system set nls_date_format='Month DD, yyyy' SCOPE=SPFILE;
alter system set nls_date_format='Month DD, yyyy' SCOPE=SPFILE
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option 

Answer:  nls_date_format isn't system changeable without a restart; this is why you couldn't do it with MEMORY, BOTH, or no options (default BOTH).

The last one with SCOPE=SPFILE is failing because you're not using an spfile; you're opening your database with a PFILE instead.

Change the value in your init.ora if you want to start using an init file (PFILE).

Furthermore, another user advised that both the db_cache_size and db_block_buffers should be set by either changing the values of db_cache_size and db_block_buffers in SPFILE using the ALTER SYSTEM command, or by re-creating the SPFILE using the CREATE SPFILE command.

alter system reset xxx scope = spfile sid = '*' 

Also see these related notes with examples on setting nls_date_format: