Hi all,
I would like to change the NLS_DATE_FORMAT parameter so that the change will be reflected in the NLS_DATABASE_PARAMETERS view. I always thought it would be alter system, but that only changes it for all newly connecting sessions. Appearantly this can also be set on a database level. What is this database level? What does it mean if the two are different? See details below.
Thanx in advance,
Lennert
SQL> select * from nls_database_parameters where parameter like
SQL> 'NLS_DA%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
SQL> select * from nls_session_parameters where parameter like
SQL> 'NLS_DA%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MM-RR
NLS_DATE_LANGUAGE DUTCH
SQL> alter system set nls_date_format='DD-MM-YYYY' scope = spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.
[...]
Database mounted.
Database opened.
SQL> select * from nls_database_parameters where parameter like
SQL> 'NLS_DA%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR ---> still the same, why?
NLS_DATE_LANGUAGE AMERICAN ---> still the same, why?
SQL> select * from nls_session_parameters where parameter like
SQL> 'NLS_DA%';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MM-YYYY ---> changed
NLS_DATE_LANGUAGE DUTCH ---> changed
SQL>