Monday, September 26, 2016

Check default NLS_DATE_FORMAT and NLS parameter in Oracle

There is a system view to check the NLS parameters.

SELECT * FROM V$NLS_PARAMETERS;

SQL> SELECT * FROM V$NLS_PARAMETERS;

PARAMETER                      VALUE
------------------------- -----------------------------
NLS_LANGUAGE              AMERICAN
NLS_TERRITORY             AMERICA
NLS_CURRENCY              $
NLS_ISO_CURRENCY          AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR              GREGORIAN
NLS_DATE_FORMAT           DD-MON-YYYY HH24:MI:SS
NLS_DATE_LANGUAGE         AMERICAN
NLS_CHARACTERSET          WE8MSWIN1252
NLS_SORT                  BINARY
NLS_TIME_FORMAT           HH.MI.SSXFF AM

PARAMETER                 VALUE
------------------------- -----------------------------
NLS_TIMESTAMP_FORMAT      DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT        HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT   DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY         $
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_COMP                  BINARY
NLS_LENGTH_SEMANTICS      BYTE
NLS_NCHAR_CONV_EXCP       FALSE

these are the default parameters which is there in oracle.

if  you want to change any one of the parameter you can do changes by using alter command.

if you want to find the individual values for the specific parameter then you need to run the query by specifying the parameter.

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'

like below :

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
in the same way you can do for the other parameters.

if you want to save the session settings permanently you need to add the option "SCOPE=SPFILE" at the end of the command. 


No comments:

Post a Comment

Thanks for giving comments!!