Oracle 10g apparently contains a number of significant changes when compared to 9i and, especially, 8i. I learned this the hard way when after completing my Solaris 10 installation of Oracle 10.2.0.1 I had a problem updating nls_date_format. I decided to encapsulate my discoveries in this web journal entry. Complete the following steps, and note that permanent changes to the database require restarting it:
sqlplus / as sysdba
create pfile='/var/tmp/pfile.txt' from spfile;
Edit /var/tmp/pfile.txt, add parameter(s):
*.nls_date_format='YYYY-MM-DD'
shutdown immediate
startup pfile='/var/tmp/pfile.txt'
Ensure change is effected (e.g. select sysdate from dual;)
create spfile from pfile='/var/tmp/pfile.txt';
shutdown immediate
startup
