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:
| Field | Interpretation |
|---|---|
ISSES_MODIFIABLE | TRUE allows a supported ALTER SESSION setting. FALSE means it cannot be changed through that route. |
ISSYS_MODIFIABLE | Describes whether a system change is dynamic and when it takes effect. |
ISPDB_MODIFIABLE | Indicates 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:
| Session | Effective format | Expected displayed text |
|---|---|---|
| A | YYYY-MM-DD | 2026-01-15 |
| B | Retained MM/DD/YYYY | 01/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.
No comments:
Post a Comment