Two connections can display the same date differently. A session override changes the presentation for that connection. The stored date stays the same.
Oracle DBA Lesson 28A — Changing a Session Setting
An Oracle parameter is a setting that controls some aspect of database behavior. Before changing one, decide which connection or database component should use the new value. A session is one connection. A session override changes behavior for that connection only.
This example uses NLS_DATE_FORMAT, the default format Oracle uses when it converts a date to text. SQL*Plus displays a DATE result with that format. Changing it changes the presentation. The stored date does not change. Oracle NLS_DATE_FORMAT reference.
Check which changes the parameter supports
V$PARAMETER describes initialization parameters and reports the effective value for the querying session. The modification flags answer different questions. Oracle V$PARAMETER reference.
| Column | What it tells you |
|---|---|
ISSES_MODIFIABLE |
Whether ALTER SESSION can change the parameter for this session. |
ISSYS_MODIFIABLE |
How a permitted ALTER SYSTEM change takes effect. |
ISPDB_MODIFIABLE |
Whether the parameter supports a separate setting in a PDB. |
ISSYS_MODIFIABLE has three important values:
| Value | System-change behavior |
|---|---|
IMMEDIATE |
An allowed system change takes effect immediately. |
DEFERRED |
An allowed system change affects subsequent sessions. |
FALSE |
The system value takes effect at instance startup. With a server parameter file, a supported change to the saved value is adopted at startup. |
Session eligibility is a separate property. For NLS_DATE_FORMAT, ISSES_MODIFIABLE is TRUE, so this session can override its current value. Use the PDB flag together with session and system eligibility to choose the operation. The PDB flag alone does not set the timing, and it does not grant privileges. System-change destinations and restart persistence come next.
SELECT name, value, isses_modifiable,
issys_modifiable, ispdb_modifiable
FROM v$parameter
WHERE name = 'nls_date_format';The account needs access to this view. In the course PDB, an administrator can grant SELECT ON SYS.V_$PARAMETER to the course account. That grant is separate from the session-format exercise.
Compare two existing connections
Open two SQL*Plus connections to the same PDB and keep both open. Record each connection's current format before you change anything:
SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';
SELECT DATE '2026-01-15' AS displayed_date FROM dual;In connection A, set a year-month-day format. Oracle ALTER SESSION reference.
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';
SELECT DATE '2026-01-15' AS displayed_date FROM dual;Connection A now reports YYYY-MM-DD, and SQL*Plus displays 2026-01-15. Repeat the date query in the already-open connection B. Its format stays as recorded. The change belongs to connection A.
The video shows connection B as 15-JAN-2026, from a starting format of DD-MON-YYYY and English date language. Your starting format can differ. Client settings can override the initial format when a connection is established. Read NLS_SESSION_PARAMETERS instead of assuming every connection starts the same way. Oracle globalization environment.
Restore the starting value
Use the value you recorded in connection A. For the video's starting value:
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY';
SELECT value
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';The override also ends when the session disconnects. For this isolated exercise, EXIT ROLLBACK closes SQL*Plus and rolls back any unfinished transaction. Commit or roll back real work according to what that connection did. A new connection gets its own instance and PDB settings, plus any client overrides.
For a report that must not depend on the session default, set the format in the query:
SELECT TO_CHAR(DATE '2026-01-15', 'YYYY-MM-DD') AS displayed_date
FROM dual;Run this on the designated course database. Record the session's actual starting format before you change it. Core examples use no separately licensed management pack.
No comments:
Post a Comment