is there a way to get NLS_DATE_FORMAT of all the sessions in oracle-CodePudding

application when connecting to oracle, at session level it sets the format 'YYYY-MM-DD HH24:MI:SS', v$parameter also has the same format. while calling a procedure, the date argument is passed like '2022-07-06 10:54:06'. most of the time it works but sometimes we get "SQLCODE = -1861". the problem continues until that session is closed by an application restart. At this moment it is suspected the NLS_DATE_FORMAT was changed for that session(its just a guess). adding to_date to the date argument while calling the procedure would most likely resolve the problem, putting a trigger to capture the NLS_DATE_FORMAT at logon time also may not help as it could be changed after login. so question is, is there a way to get the NLS_DATE_FORMAT of all sessions on an instance? or is there a way to change the NLS_DATE_FORMAT of an already active session from another session?

CodePudding user response：

is there a way to get the NLS_DATE_FORMAT of all sessions on an instance?

For your own session (which is what you actually want, based on the comment), you'd query v$nls_parameters:

SQL> select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER            VALUE
-------------------- --------------------
NLS_DATE_FORMAT      dd.mm.yyyy hh24:mi

SQL>

As of you viewing information about other sessions, you can't do that easily - see what Sergiusz Wolicki from Oracle says:

(...) this information is not easily available. NLS environment is kept in an UGA variable and other sessions to not have access to it. Skilled Oracle engineers would be able to get this information from an UGA dump but otherwise it is not retrievable.

I'm not one of "skilled Oracle engineers" so ... I'm afraid I can't help any further, sorry.