NLS_DATE_FORMAT
Hi,
for some reason, we need to change the NLS_DATE_FORMAT in our database specifically for each sessions. I had a suggestion of implementing it by running ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS statement from a trigger created on DATABASE (AFTER LOGON trigger). But they wanted me to change this parameter value which I promptly did. I changed my init.ora and added NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS' and restarted my instance and then created spfile and again restarted using spfile.( I know..I know .. I could have run Alter system ....scope =spfile and then I was getting an error even though I was starting my DB using spfile). But when I run "select sysdate from dual" , I get date in DD-MON-YY format (the default format). Here I have few doubts:
1. Is there a problem in implementing this via a trigger for each session? If yes, what problem?
2. Why is that even after making the change in the spfile/pfile, I am getting date value in default format?
3. Even after starting my database using spfile, why cant I modify this parameter with scope=spfile?
SYS@TP92>create spfile from pfile='C:\oracle\admin\tp92\pfile\init.ora';
create spfile from pfile='C:\oracle\admin\tp92\pfile\init.ora'
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
SYS@TP92>alter system set nls_date_format='dd/mm/yyyy hh24:mi:ss' scope=spfile;
alter system set nls_date_format='dd/mm/yyyy hh24:mi:ss' scope=spfile
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
TIA,
Onkar