SHOW PARAMETER in Oracle 23c/23ai: Practical Examples

In SQL*Plus or SQLcl, SHOW PARAMETER is the quickest way to view the current (runtime) value of initialization parameters—whether they originated from an SPFILE, a PFILE, or were changed dynamically. It performs a case-insensitive substring match over parameter names.

-- list all parameters currently in effect
SHOW PARAMETER

-- filter by name substring (case-insensitive)
SHOW PARAMETER processes
SHOW PARAMETER memory
SHOW PARAMETER control_files

• Matching: behaves like %substring% against NAME.
• Singular form: The supported command is SHOW PARAMETER (singular).
• CDB/PDB scope: In a multitenant database, the values you see depend on the current container (CDB root vs. PDB). Use SHOW CON_NAME to confirm context, or switch with ALTER SESSION SET CONTAINER = <pdb_name>.

Purpose	Example	What you’ll learn
View all parameters	SHOW PARAMETER	Full list of current settings and values
Processes & sessions	SHOW PARAMETER processes SHOW PARAMETER sessions SHOW PARAMETER open_cursors	Process cap, session cap, per-session cursor limit
Memory (auto-tuning)	SHOW PARAMETER memory SHOW PARAMETER sga_target SHOW PARAMETER pga_aggregate_target	Overall memory targets and SGA/PGA sizing signals
Storage	SHOW PARAMETER control_files SHOW PARAMETER db_block_size SHOW PARAMETER undo	Control file list, block size, undo tablespace/retention
Diagnostics	SHOW PARAMETER diagnostic_dest	Base directory for alert log, traces, dumps
Optimizer	SHOW PARAMETER optimizer	Mode, features enablement, costs-related flags
Archiving	SHOW PARAMETER log_archive	Redo log archive destinations and related options
NLS	SHOW PARAMETER nls	Language, territory, date/currency formats
Parallel execution	SHOW PARAMETER parallel	Default DOP and PX behavior
Job scheduler	SHOW PARAMETER job_queue_processes	Max job queue workers
Services	SHOW PARAMETER service_names	Registered service names
CDB/PDB awareness	SHOW PARAMETER pdb	PDB-specific toggles and limits

SQL> SHOW PARAMETER processes

NAME                  TYPE     VALUE
--------------------  -------  -----
processes             integer  300

Columns typically include NAME, TYPE, and VALUE. For deeper context (defaults, session/system modifiability, PDB scope), query the dynamic views below.

• V$PARAMETER – session view of parameters; includes ISDEFAULT, ISSYS_MODIFIABLE, ISSES_MODIFIABLE, ISPDB_MODIFIABLE, and DESCRIPTION.
• V$SYSTEM_PARAMETER – system-wide values (useful for instance perspective).
• V$PARAMETER2 – one row per ordinal element when a parameter accepts multiple values (e.g., lists).
• V$SPPARAMETER – values stored in the SPFILE (what will persist across restarts).

-- find session-visible values and whether they are modifiable
SELECT name, value, isdefault, issys_modifiable, issses_modifiable, ispdb_modifiable
FROM   v$parameter
WHERE  LOWER(name) LIKE '%process%';

• Check container first: SHOW CON_NAME then SHOW PARAMETER ... to avoid confusing root vs. PDB values.
• Know the scope: Many parameters are dynamic (modifiable at session/system level); others are static (require restart). Use the columns in V$PARAMETER to verify.
• Auto memory: With memory auto-tuning, treat MEMORY_TARGET/MEMORY_MAX_TARGET, SGA_TARGET, and PGA_AGGREGATE_LIMIT as key dials; measure before/after.
• Persisting changes: ALTER SYSTEM SET ... SCOPE=SPFILE persists; SCOPE=MEMORY is ephemeral; SCOPE=BOTH does both (SPFILE + current runtime).
• List parameters by theme: Use filters like SHOW PARAMETER undo, ... nls, ... optimizer to navigate quickly.

Two quick checks—one exact name, one substring filter:

SQL> show parameter user_dump_dest
NAME	TYPE	VALUE
user_dump_dest	string	e:\oracle\admin\COIN\udump
SQL> show parameter dump_dest
NAME	TYPE	VALUE
background_dump_dest	string	e:\oracle\admin\COIN\bdump
user_dump_dest	string	e:\oracle\admin\COIN\udump

What happened?

show parameter user_dump_dest	Exact match returns one row.
TYPE = string	Data type of the parameter.
VALUE	Current runtime value.
show parameter dump_dest	Substring match returns both background_dump_dest and user_dump_dest.

In the next lesson, you’ll explore parameter metadata in more depth using V$PARAMETER and related views.