How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g

It is really really easy to change the default NLS_DATE_FORMAT setting but to be honest, you should set it at a session level IMHO.

We basically just need to run “ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY-MM-DD’ SCOPE=SPFILE” as a user with sysdba privileges. If you started the Oracle instance without a spfile (it should be located at $ORACLE_HOME/dbs/spfile[instance name].ora), you will receive the ORA-32001 error.

ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Just create a new spfile, restart:

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
create spfile='/oracle/10g/dbs/spfileUAT2.ora' from pfile='/oracle/10g/dbs/initUAT2.ora';

*restart*

select INSTANCE_NAME from v$instance;
INSTANCE_NAME
----------------
UAT2
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD' SCOPE=SPFILE;
System altered.

*restart*

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';
VALUE
----------------------------------------------------------------
YYYY-MM-DD

That’s it. 🙂

Share Button

FW: Revision 3: System Episode 45 – Practically Free DIY WiFi Antennas


The building of the free windsurfer (using the Ez-12 Parabolic Reflector Template) from freeantennas.com is particularly interesting. The website claims an amplification of about 9 dBi of gain with the standard size template and 12 dBi of gain if you double the size. Something I will definitely try tonight 🙂

Share Button