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.