HOW TO CHECK NLS SETTINGS ON DATABASE, INSTANCE AND SESSION LEVEL

 
REQUEST

 
QUERY

SET PAGES 999
SET LINES 300
COL PARAMETER FOR A30
COL DATABASE FOR A30
COL INSTANCE FOR A30
COL SESSION FOR A30
SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION"
FROM   NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S
WHERE  DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+)
ORDER BY 1;

PARAMETER                      DATABASE                       INSTANCE                       SESSION
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_CALENDAR                   GREGORIAN                                                     GREGORIAN
NLS_CHARACTERSET               AL32UTF8
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_CURRENCY                   $                                                             $
NLS_DATE_FORMAT                DD-MON-RR                                                     DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN                                                      AMERICAN
NLS_DUAL_CURRENCY              $                                                             $
NLS_ISO_CURRENCY               AMERICA                                                       AMERICA
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_LENGTH_SEMANTICS           BYTE                           BYTE                           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_NUMERIC_CHARACTERS         .,                                                            .,
NLS_RDBMS_VERSION              12.2.0.1.0
NLS_SORT                       BINARY                                                        BINARY
NLS_TERRITORY                  AMERICA                        AMERICA                        AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                      DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                            HH.MI.SSXFF AM TZR