Print Friendly, PDF & Email
A database query to fetch Oracle NLS settings defined on a database, an instance, and session levels.
QUERY

For instance, modify some NLS settings on a session-level by the following statements

ALTER SESSION SET NLS_TERRITORY='KOREA';
ALTER SESSION SET NLS_LANGUAGE='KOREAN';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
ALTER SESSION SET NLS_TIME_FORMAT='HH24:MI:SS';
ALTER SESSION SET NLS_DUAL_CURRENCY='₩';

Use the following query to fetch current NLS settings

SET PAGES 999
SET LINES 300
COL PARAMETER FOR A30
COL DATABASE FOR A30
COL INSTANCE FOR A30
COL SESSION FOR A30

SELECT ND.PARAMETER, 
       ND.VALUE "DATABASE", 
       NI.VALUE "INSTANCE",
       NS.VALUE "SESSION"
FROM
       NLS_DATABASE_PARAMETERS ND, 
       NLS_INSTANCE_PARAMETERS NI, 
       NLS_SESSION_PARAMETERS NS
WHERE
       ND.PARAMETER=NI.PARAMETER(+) AND 
       ND.PARAMETER=NS.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-YYYY
NLS_DATE_LANGUAGE              AMERICAN                                                      KOREAN
NLS_DUAL_CURRENCY              $                                                             ₩
NLS_ISO_CURRENCY               AMERICA                                                       KOREA
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       KOREAN
NLS_LENGTH_SEMANTICS           BYTE                           BYTE                           BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_NUMERIC_CHARACTERS         .,                                                            .,
NLS_RDBMS_VERSION              19.0.0.0.0
NLS_SORT                       BINARY                                                        BINARY
NLS_TERRITORY                  AMERICA                        AMERICA                        KOREA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                      RR/MM/DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                  RR/MM/DD HH24:MI:SSXFF TZR
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                HH24:MI:SS
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                            HH24:MI:SSXFF TZR

20 행이 선택되었습니다.

Written At
06 NOV 202111:20
OEL Release
7.9 x64
Database Release
19.12.0.0.0