version: 11.2.0.4.0
OS: Solaris 11
hello, we faced a new issue in our database today regarding NLS_DATE_FORMAT.
when i queried nls_session_parameters, for one schema the value is set to "MM/DD/YYYY HH24:MI:SS". FOr another schema it is set to "DD-MON-RR".
when i queried v$parameter the nls_date_format is set to "MM/DD/YYYY HH24:MI:SS".
In the schema with "DD-MON-RR", the format is changed when we do alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS'.
instead of executing alter session command all the time, how can we make it as a one time change.
i tried it in the sqldeveloper by going to tools>preferences>database and changing the NLS value to MM/DD/YYYY HH24:MI:SS, still the format is shown as DD-MON-RR.
question1: how to permanently change the date format to "MM/DD/YYYY HH24:MI:SS" for the schema which has the format "DD-MON-RR".
i tried changing the NLS value in sqldeveloper,
i tried adding the environment variable NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS' on the client side.
but what ever i do it seems the schema with the NLS_DATE_FORMAT seems to go back to 'DD-MON-RR' and not 'MM/DD/YYYY HH24:MI:SS'
any light on this issue is appreciated.
Thank You