Change a parameter (with SCOPE)

Command

-- Both the running instance and the spfile (default, dynamic parameters):
ALTER SYSTEM SET open_cursors=500 SCOPE=BOTH;

-- Only for the next restart (static parameters):
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

-- Immediate only, do not write to the spfile:
ALTER SYSTEM SET optimizer_mode='ALL_ROWS' SCOPE=MEMORY;

-- View the current value:
SHOW PARAMETER open_cursors;

What it does

Changes an init parameter in the running instance and/or the spfile. SCOPE determines the behavior.

When to use it

For tuning, capacity adjustments, and error fixes that require a parameter change.

Caution

Static parameters (processes, sga_max_size...) only accept SCOPE=SPFILE and require a restart. A wrong value can make the instance unable to start; take a pfile backup first for critical parameters.