Enhance Skills Daily

October 02, 2026

Oracle DBA Lesson 28 Part 1 — Check Which Settings a Session Can Change

A parameter change needs a clear owner: the current session, a system setting, or a setting inside a particular pluggable database. Before changing anything, check that the parameter supports the intended operation and that you are connected to the intended container.

Oracle DBA Lesson 28 Part 1 — Check Which Settings a Session Can Change

Session eligibility and effective settings

A session is the database connection in which your work runs. Some initialization parameters can be overridden for that session. ALTER SESSION changes supported settings for the current connection. Its effects end when the connection ends. Another session retains its own settings. See ALTER SESSION.

Ordinary date-format changes do not require the ALTER SESSION privilege needed for certain other operations, such as tracing.

Confirm context:

SHOW USER
SHOW CON_NAME

SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name
FROM dual;

Inspect eligibility for the named initialization parameter:

SELECT name, value,
       isses_modifiable, issys_modifiable, ispdb_modifiable
FROM v$parameter
WHERE name = 'nls_date_format';

V$PARAMETER describes initialization parameters in effect for the querying session. Its fields answer different questions:

FieldInterpretation
ISSES_MODIFIABLETRUE allows a supported ALTER SESSION setting. FALSE means it cannot be changed through that route.
ISSYS_MODIFIABLEDescribes whether a system change is dynamic and when it takes effect.
ISPDB_MODIFIABLEIndicates whether the parameter can be modified inside a PDB. Check the individual parameter and operation.

See V$PARAMETER.

The system timing values are:

  • IMMEDIATE: a supported system change can take effect dynamically.
  • DEFERRED: a supported system change affects subsequent sessions. Existing sessions retain their values.
  • FALSE: the system value cannot change dynamically. A permitted saved change requires an SPFILE arrangement and takes effect in a subsequent instance. Consult the parameter's full documentation.

Interpret session eligibility independently of system timing. Metadata eligibility does not grant database privileges. See ALTER SYSTEM.

Oracle documents NLS_DATE_FORMAT as modifiable with ALTER SESSION and modifiable in a PDB. For its effective NLS value, use the session-specific view. See NLS_DATE_FORMAT.

SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

NLS_SESSION_PARAMETERS lists the NLS settings of the user session. Capture this value independently in each connection. Client and application initialization can affect the initial NLS environment. Do not guess it from an instance default. See NLS_SESSION_PARAMETERS.

A two-session comparison

Use two independent SQL*Plus connections to the same intended application PDB. Both start with MM/DD/YYYY and use a Gregorian calendar. These are not universal defaults.

Both sessions query the same date:

SELECT DATE '2026-01-15' AS displayed_date
FROM dual;

The ANSI DATE literal supplies 15 January 2026 in the required YYYY-MM-DD syntax. It keeps the date value constant while the displayed representation depends on each session's format. See datetime literals.

Change Session A only:

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';

Repeat the same date query in both sessions. The expected comparison is:

SessionEffective formatExpected displayed text
AYYYY-MM-DD2026-01-15
BRetained MM/DD/YYYY01/15/2026

The date value is the same. Only Session A's default date-to-text representation changed. Session B provides the comparison that makes the connection boundary clear.

Query NLS_SESSION_PARAMETERS again in both connections and record the actual results. Compare the effective format with the displayed date. If they do not match the expected case, investigate the current connection, client formatting, the NLS calendar, and any application logic that resets session settings.

Repeatable report text

When a report requires a particular date representation, supply its format in the query:

SELECT TO_CHAR(DATE '2026-01-15', 'YYYY-MM-DD') AS report_date
FROM dual;

TO_CHAR converts the date into text using the supplied format. With a Gregorian calendar, both sessions produce 2026-01-15, even though their default date formats differ. An explicit format makes this report's presentation predictable. Other formatting requirements, such as month-name language or calendar behavior, should also be specified or controlled when relevant. See TO_CHAR (datetime).

A new connection initializes its own settings from applicable instance and PDB defaults and from client or application initialization. It does not inherit Session A's override. Inspect its effective NLS settings when it connects. See NLS setting precedence.

Quiz

1. Which field answers whether an initialization parameter can be changed with ALTER SESSION?

2. What does ISSYS_MODIFIABLE = DEFERRED establish for an allowed system change?

3. Session A changes its NLS_DATE_FORMAT. What should the comparison with Session B establish?

4. Which query directly inspects the effective NLS date format of the current user session?

5. Does ALTER SESSION save this format change for the next database restart?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...