NLS_SESSION_PARAMETERS - Check Oracle NLS Session Settings

NLS_SESSION_PARAMETERS

Overview

NLS_SESSION_PARAMETERS is a fixed data dictionary view that returns the National Language Support (NLS) settings currently in effect for the calling session. It shows session-level overrides applied via ALTER SESSION SET NLS_*, which take precedence over instance-level parameters (from NLS_INSTANCE_PARAMETERS, set via ALTER SYSTEM or the init.ora) and database-level defaults (from NLS_DATABASE_PARAMETERS, set at CREATE DATABASE time).

DBAs and developers query this view to diagnose date conversion errors (ORA-01861), unexpected string comparison behaviour, character set mismatches, and locale-dependent sort order issues. Understanding the NLS hierarchy — database defaults overridden by instance settings overridden by session settings — is fundamental to troubleshooting application data-correctness bugs that appear in some client environments but not others.

View Type: Data Dictionary View (session-specific) Available Since: Oracle 7 Required Privileges: No special privileges required — every user can query their own session’s NLS settings

Key Columns

Column	Datatype	Description
PARAMETER	VARCHAR2(64)	NLS parameter name (e.g., NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_TERRITORY)
VALUE	VARCHAR2(64)	Current value of the parameter for this session; reflects ALTER SESSION overrides if applied

NLS Parameters Reference

The following parameters appear as rows in NLS_SESSION_PARAMETERS:

Parameter	Example Value	Purpose
NLS_LANGUAGE	AMERICAN	Language for messages, day names, month names, and default sort
NLS_TERRITORY	AMERICA	Territory for defaults such as date format, currency symbol, and first day of week
NLS_CURRENCY	$	Local currency symbol used with the L format mask
NLS_ISO_CURRENCY	AMERICA	ISO currency abbreviation (e.g., USD) used with the C format mask
NLS_NUMERIC_CHARACTERS	.,	Decimal separator and group separator characters
NLS_DATE_FORMAT	DD-MON-RR	Default date format applied when no explicit TO_DATE/TO_CHAR format mask is provided
NLS_DATE_LANGUAGE	AMERICAN	Language for day and month names in date strings
NLS_SORT	BINARY	Sort sequence: BINARY (byte-order) or a linguistic sort such as GENERIC_M or XGERMAN
NLS_COMP	BINARY	Comparison semantics: BINARY or LINGUISTIC; affects WHERE clause string comparisons
NLS_CHARACTERSET	AL32UTF8	Database character set — this is fixed at the database level and is NOT overridable per session
NLS_NCHAR_CHARACTERSET	AL16UTF16	National character set for NCHAR/NVARCHAR2/NCLOB columns
NLS_TIME_FORMAT	HH.MI.SSXFF AM	Default format mask for TIME values
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM	Default format mask for TIMESTAMP values
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR	Default format mask for TIMESTAMP WITH TIME ZONE values
NLS_DUAL_CURRENCY	$	Dual currency symbol for EU transition support
NLS_CALENDAR	GREGORIAN	Calendar system (GREGORIAN, JAPANESE IMPERIAL, THAI BUDDHA, etc.)

Essential Queries

Basic Usage

Show all current NLS settings for the session in a readable format:

SELECT
    parameter,
    value
FROM
    nls_session_parameters
ORDER BY
    parameter;

Compare Session vs Database vs Instance Settings

Show where each NLS value originates — session override, instance default, or database default — to diagnose inconsistencies across environments:

SELECT
    sp.parameter,
    sp.value                                    AS session_value,
    ip.value                                    AS instance_value,
    dp.value                                    AS database_value,
    CASE
        WHEN sp.value != dp.value AND sp.value = ip.value THEN 'Instance override'
        WHEN sp.value != dp.value               THEN 'Session override'
        ELSE 'Database default'
    END                                         AS override_level
FROM
    nls_session_parameters  sp
    JOIN nls_instance_parameters ip ON ip.parameter = sp.parameter
    JOIN nls_database_parameters dp ON dp.parameter = sp.parameter
ORDER BY
    sp.parameter;

Date Format and Language Settings Only

Focus on the date and time NLS parameters most frequently involved in ORA-01861 and ORA-01843 errors:

SELECT
    parameter,
    value
FROM
    nls_session_parameters
WHERE
    parameter IN (
        'NLS_DATE_FORMAT',
        'NLS_DATE_LANGUAGE',
        'NLS_TIMESTAMP_FORMAT',
        'NLS_TIMESTAMP_TZ_FORMAT',
        'NLS_CALENDAR',
        'NLS_LANGUAGE',
        'NLS_TERRITORY'
    )
ORDER BY
    parameter;

Verify Case-Insensitive Query Settings

Before running queries that depend on NLS_COMP and NLS_SORT for case-insensitive or accent-insensitive comparisons, confirm the session is correctly configured:

-- Check current comparison and sort settings
SELECT
    parameter,
    value
FROM
    nls_session_parameters
WHERE
    parameter IN ('NLS_COMP', 'NLS_SORT', 'NLS_LANGUAGE');

-- Apply case-insensitive settings for the session
-- ALTER SESSION SET NLS_COMP = LINGUISTIC;
-- ALTER SESSION SET NLS_SORT = BINARY_CI;  -- Case-insensitive
-- ALTER SESSION SET NLS_SORT = BINARY_AI;  -- Accent and case insensitive

-- Test the effect:
-- SELECT * FROM employees WHERE last_name = 'smith';
-- With NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI, this matches 'Smith', 'SMITH', 'smith'

Script to Set Standard Session NLS for DBA Work

Apply a consistent set of NLS parameters for interactive DBA sessions to avoid accidental date format errors:

-- Show what will be set before applying
SELECT parameter, value AS current_value FROM nls_session_parameters
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_NUMERIC_CHARACTERS','NLS_LANGUAGE');

-- Apply standard DBA NLS settings
ALTER SESSION SET NLS_DATE_FORMAT     = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
ALTER SESSION SET NLS_LANGUAGE        = 'AMERICAN';
ALTER SESSION SET NLS_TERRITORY       = 'AMERICA';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

-- Verify the changes took effect
SELECT parameter, value
FROM   nls_session_parameters
WHERE  parameter IN (
    'NLS_DATE_FORMAT',
    'NLS_TIMESTAMP_FORMAT',
    'NLS_LANGUAGE',
    'NLS_TERRITORY',
    'NLS_NUMERIC_CHARACTERS'
);

Common Use Cases

• Diagnosing ORA-01861 (literal does not match format string) — This error fires when an implicit date conversion fails because the string being inserted or compared does not match NLS_DATE_FORMAT; query NLS_SESSION_PARAMETERS to see the format in effect, then either fix the application to use explicit TO_DATE() calls or align the NLS_DATE_FORMAT with what the application expects
• Diagnosing ORA-01843 (not a valid month) — Often caused by a mismatch between NLS_DATE_LANGUAGE (which governs month name parsing) and the language of the month abbreviations in the data; verify NLS_DATE_LANGUAGE is set to match the data’s locale
• Case-insensitive query setup — Setting NLS_COMP = LINGUISTIC and NLS_SORT = BINARY_CI makes standard SQL comparisons and ORDER BY case-insensitive without rewriting queries; confirm the session values here before enabling indexes built with the corresponding SYS_NLSSORT expression
• Multi-language application testing — When testing an application that connects with different NLS_LANGUAGE settings (e.g., GERMAN for European users, AMERICAN for US users), query NLS_SESSION_PARAMETERS at the start of each test run to confirm the correct locale is active
• Character set compatibility checks — Although NLS_CHARACTERSET cannot be changed per session, verifying it here alongside the client’s configured character set (NLS_LANG environment variable) helps diagnose character corruption issues where multibyte characters are stored incorrectly
• Number format debugging — Applications receiving ORA-01722 (invalid number) from numeric string conversions often have a mismatch in NLS_NUMERIC_CHARACTERS; verify whether the session expects a period or comma as the decimal separator

Related Views

• V$PARAMETER — Shows instance-level initialisation parameters including NLS_DATE_FORMAT, NLS_LANGUAGE, and NLS_TERRITORY as set in the init.ora or spfile
• V$SESSION — Contains NLS_LANGUAGE and NLS_TERRITORY columns (from Oracle 12c) showing the language and territory for each connected session without requiring a join
• NLS_INSTANCE_PARAMETERS — Instance-level NLS settings; the middle tier of the three-level hierarchy (database → instance → session)
• NLS_DATABASE_PARAMETERS — Database-level NLS defaults set at CREATE DATABASE time; includes the fixed NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET values
• V$NLS_VALID_VALUES — Lists all valid values for each NLS parameter; use to look up valid NLS_SORT or NLS_LANGUAGE options before issuing ALTER SESSION

Version Notes

• Oracle 7: NLS_SESSION_PARAMETERS introduced with core parameters NLS_LANGUAGE, NLS_TERRITORY, NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, and NLS_SORT
• Oracle 8i: NLS_COMP parameter added; NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT added with TIMESTAMP datatype support
• Oracle 9i: NLS_CALENDAR expanded to support additional calendar systems; NLS_DUAL_CURRENCY added for European monetary union support
• Oracle 10g: NLS_COMP values extended to include LINGUISTIC for linguistic-aware string comparisons; BINARY_CI and BINARY_AI sort options introduced
• Oracle 12c Multitenant: PDB-level NLS settings possible via ALTER PLUGGABLE DATABASE; NLS_SESSION_PARAMETERS always reflects the current session’s effective values regardless of PDB or CDB context
• Oracle 19c: No structural changes; NLS parameters interact with In-Memory Column Store expression evaluation; INMEMORY queries honour NLS_COMP and NLS_SORT settings
• Oracle 21c / 23ai: No structural changes; JSON Relational Duality Views and new SQL domains respect NLS_DATE_FORMAT when converting date columns; always verify NLS settings in sessions processing JSON date strings