A system setting can work today and return to its saved value after restart. Choose the destination, then check both values.
Oracle DBA Lesson 28B — Change Settings Now or at Restart
An instance reads initialization settings when it starts. While it runs, a dynamic parameter can differ from the value saved for the next startup. That is why a temporary change can disappear after restart.
V$SYSTEM_PARAMETER shows the value currently in effect for the instance. V$SPPARAMETER shows entries saved in the server parameter file. V$PARAMETER shows the value effective for the session that runs the query. Those three answers are not the same question.
Where ALTER SYSTEM writes the change
These scopes apply to ALTER SYSTEM in CDB$ROOT. Oracle 19c ALTER SYSTEM.
| Destination | Clause | Effect |
|---|---|---|
| Running value | SCOPE=MEMORY |
Updates memory on the parameter's dynamic timing. The saved startup value stays as it was. |
| Next startup | SCOPE=SPFILE |
Updates the SPFILE. The new value is used at the next instance restart. |
| Both | SCOPE=BOTH |
Updates memory and the SPFILE when a dynamic change is allowed. |
SPFILE and BOTH need an active SPFILE. If the instance started from a PFILE, the available scope is MEMORY. With an SPFILE, the default scope is BOTH. With a PFILE, the default is MEMORY. Write the scope you mean. Do not rely on the default.
SHOW PARAMETER spfileA path in VALUE is the active SPFILE. In the video that path is /lab/config/spfilecourse.ora. A blank VALUE means this instance started from a PFILE. A PFILE startup has no SPFILE to update, so route the change through SCOPE=MEMORY.
When the new value becomes active
Read ISSYS_MODIFIABLE before you pick a scope.
| ISSYS_MODIFIABLE | What to do |
|---|---|
IMMEDIATE |
An allowed dynamic system change takes effect immediately. |
DEFERRED |
An allowed system change affects sessions that start after it. Use the DEFERRED keyword. Sessions already connected keep their current session value. |
FALSE |
The system value is startup-only. With an active SPFILE, save it with SCOPE=SPFILE and restart to activate it. The running instance does not adopt that value. |
Session modifiability is a separate flag. A parameter can be static at system level and still allow ALTER SESSION. ISSYS_MODIFIABLE = FALSE does not by itself answer the session question.
A temporary OPEN_CURSORS change
A cursor is the handle a session uses for a private SQL area while it processes a statement. OPEN_CURSORS is the maximum number of those open handles for one session. The right limit depends on the application. The video uses 300 as the baseline and 400 as the test. The Oracle 19c documented default is 50. OPEN_CURSORS reference.
In the video example, in CDB$ROOT:
- Current
OPEN_CURSORSis 300, andISSYS_MODIFIABLEisIMMEDIATE. - The instance is using an SPFILE. The saved row is
SID*, value 300,ISSPECIFIEDTRUE. - No instance-specific saved row overrides that wildcard at startup.
Record the current value and every saved row before you change anything:
SELECT name, value, issys_modifiable, ispdb_modifiable, con_id
FROM v$system_parameter
WHERE name = 'open_cursors';
SELECT sid, name, value, isspecified, ordinal, con_id
FROM v$spparameter
WHERE name = 'open_cursors'
ORDER BY con_id, sid, ordinal;ISSPECIFIED = TRUE is an explicit saved entry. ISSPECIFIED = FALSE means that row is not an explicit entry; a default may be in use. Read SID and CON_ID before you decide which saved value this instance will use. A row for a specific instance name takes precedence over SID = *. If the instance started from a PFILE, V$SPPARAMETER shows ISSPECIFIED = FALSE and does not describe an active startup SPFILE. SID precedence.
On the designated course database, after the current value is recorded:
ALTER SYSTEM SET open_cursors=400 SCOPE=MEMORY;Query both views again. In the video, current becomes 400 and the saved value stays 300. The live value changed. The SPFILE entry did not.
Put the recorded live value back with MEMORY. In the video that value is 300. In your lab, use the number you captured. It may already differ from the saved value. Restoration must keep that original difference.
ALTER SYSTEM SET open_cursors=300 SCOPE=MEMORY;Query both views again. The video ends at current 300 and saved 300. If the session drops after the test change, reconnect, confirm you are still in CDB$ROOT, set the recorded live value with SCOPE=MEMORY, and check both views.
Container context
The commands above run in CDB$ROOT. CONTAINER=CURRENT is the default. A root setting also applies to PDBs that inherit it, so a root change is not limited to the root session that issued it.
OPEN_CURSORS is PDB-modifiable. A PDB that has its own value does not have to match the inherited root value. CONTAINER=ALL changes the setting across PDBs. That is outside this exercise.
Inside a PDB, check ISPDB_MODIFIABLE and that parameter's documented behavior before you copy the root procedure. A PDB override is stored with the PDB. It can take effect when that PDB is closed and opened again. The root rule here is different: an SPFILE change in the root waits for an instance restart.
Practice
Use the course's separate disposable Oracle 19c CDB, single instance, SQL*Plus. Do not run this against a production database. The account needs ALTER SYSTEM and access to V$SYSTEM_PARAMETER and V$SPPARAMETER.
Before the change, confirm the user, the database, and CDB$ROOT. Capture the active SPFILE path, the current value, every applicable saved row, and ISSYS_MODIFIABLE. Agree the test value with the instructor. Keep other configuration changes out of this test. This exercise does not need a restart, an SPFILE write, a hidden parameter, or a management pack.
Keep the captured number somewhere outside the session before you change it. If a statement fails, restore that exact number with SCOPE=MEMORY and verify current and saved state. Success is three pieces of evidence: the baseline, current 400 with saved still at the original entry, and the restored live value with the SPFILE entry unchanged.
No comments:
Post a Comment