Lesson 7	Checking database parameter values
Objective	Use the SHOW PARAMETER command to display parameter values.

Relevant Examples for the SHOW PARAMETER command in Oracle 23c

In Oracle 23c, the `SHOW PARAMETER` command is very useful for displaying the current values of initialization parameters, whether they are from `SPFILE`, `PFILE`, or dynamically modified settings.
Here’s a structured list of relevant examples you would typically use:
General Form

SHOW PARAMETER <search_string>;

- "search_string" can be partial or complete (Oracle treats it case-insensitively and does pattern matching).
Common and Relevant Examples

Purpose	Command Example	Description
View all parameters	`SHOW PARAMETER`	Displays all parameters currently in effect.
View memory parameters	`SHOW PARAMETER memory`	Displays parameters related to memory (SGA, PGA, MEMORY_TARGET, etc.).
View processes limits	`SHOW PARAMETER processes`	Displays the maximum number of OS processes Oracle can use.
View control file settings	`SHOW PARAMETER control_files`	Displays the control file locations.
View undo settings	`SHOW PARAMETER undo`	Displays undo tablespace, retention settings.
View diagnostic destination	`SHOW PARAMETER diagnostic_dest`	Shows where trace files, alert logs, and dump files are stored.
View optimizer settings	`SHOW PARAMETER optimizer`	Displays optimizer mode, optimizer features enable, etc.
View database block size	`SHOW PARAMETER db_block_size`	Displays the size of Oracle database blocks.
View open cursors limit	`SHOW PARAMETER open_cursors`	Displays the maximum number of cursors per session.
View archive log settings	`SHOW PARAMETER log_archive`	Displays settings for archiving redo logs.
View audit settings	`SHOW PARAMETER audit`	Displays database auditing settings.
View default tablespace	`SHOW PARAMETER default_tablespace`	Displays default permanent tablespace for new users.
View NLS settings (language/territory)	`SHOW PARAMETER nls`	Displays National Language Support parameters.
View parallelism settings	`SHOW PARAMETER parallel`	Displays degree of parallelism and related options.
View job scheduler settings	`SHOW PARAMETER job_queue_processes`	Displays maximum number of job queue processes.
View resource manager settings	`SHOW PARAMETER resource_manager`	Displays settings related to Resource Manager.
View service names	`SHOW PARAMETER service_names`	Displays configured service names.
View session limits	`SHOW PARAMETER sessions`	Displays maximum concurrent sessions allowed.
View PGA settings	`SHOW PARAMETER pga_aggregate_target`	Displays target for PGA memory usage.
View SGA settings	`SHOW PARAMETER sga_target`	Displays target for SGA memory.

Tip for Oracle 23c

You can use SHOW PARAMETERS (plural) interchangeably with SHOW PARAMETER.
Oracle 23c expands memory auto-tuning, so parameters like memory_max_target, memory_target, and pga_aggregate_limit are even more important.
In Oracle 23c CDB/PDB environments, it’s often useful to also check:
```
      SHOW PARAMETER pdb;
    
```
to view PDB-specific parameters.

Sample output format
When you run:

SHOW PARAMETER processes;

Oracle returns:

NAME	TYPE	VALUE
processes	integer	300

Legacy Diagram for show parameter

show-parameter-examples — At the top of the tree is the root node labeled:
- SHOW PARAMETER
From this root, four main branches split off, each representing a major category of parameters:

**Memory**

`SHOW PARAMETER MEMORY`

`SHOW PARAMETER PGA_AGGREGATE_TARGET`

`SHOW PARAMETER SGA_TARGET`

`SHOW PARAMETER MEMORY_TARGET`

*(This branch focuses on Oracle’s memory management, both PGA, SGA, and overall automatic memory settings.)*

**Storage**

`SHOW PARAMETER CONTROL_FILES`

`SHOW PARAMETER UNDO`

`SHOW PARAMETER DB_BLOCK_SIZE`

*(This branch shows parameters dealing with file management, undo information, and data block size.)*

**Sessions**

`SHOW PARAMETER PROCESSES`

`SHOW PARAMETER OPEN_CURSORS`

`SHOW PARAMETER SESSIONS`

*(This branch covers session control, including process limits, cursor management, and session scaling.)*

**Miscellaneous**

`SHOW PARAMETER DIAGNOSTIC_DEST`

`SHOW PARAMETER OPTIMIZER`

`SHOW PARAMETER NLS`

`SHOW PARAMETER PARALLEL`

(This branch includes important miscellaneous settings for diagnostics, optimizer behavior, language/locale (NLS), and parallel execution.)
Each branch organizes related SHOW PARAMETER queries under a logical grouping based on what aspect of the database they control or influence. The layout is clear, top-down, and helps administrators quickly find and explore parameters by theme (Memory, Storage, Sessions, or Miscellaneous).

Database parameters are initially set in the database parameter file, but many of them can be changed while the database is running. To view the current value of any parameter, you can issue the SHOW PARAMETER command. SHOW PARAMETER is a Server Manager command, and takes the following form:

SHOW PARAMETER [parameter_name]

Use SHOW PARAMETER by itself to get a list of all the current parameter settings. Supply a parameter name as an argument, and you will see the value for only that one parameter. Pass a partial parameter name as an argument, and you'll see values for all parameters with names containing the string that you pass. The following mouseover shows two examples of this command being used:

Image followed by Table

NAME	TYPE	VALUE
SQL> show parameter user_dump_dest
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

show-parameter-user_dump_dest

Show Parameter

show parameter user_dump_dest	This command is asking to see the value of a specific parameter.
First occurrence of "user_dump_dest	Server Manager responds by displaying the value of the specified parameter.
string	This tells you that the parameter contains a character string value.
e:\oracle...	This is the value of the parameter.
show parameter dump_dest	This command specifies a partial parameter name. Server Manager will display values for all parameters with names containing the specified string.
background_dump_dest	This name contains the string "dump_dest", so Server Manager displays its value.
user_dump_dest	This name also contains the string "dump_dest", so Server Manager displays its value as well.
Both occurrences of "string"	Both parameters contain character string values.
The two values	These are the values for the two parameters.

Parameter Passed

The parameter passed to the first command is user_dump_dest, so the value of that parameter is displayed. The parameter passed to the second command is dump_dest. There are two parameters with the string "dump_dest" in their name, so the values of both are displayed. In the next lesson, you will learn more about database parameters by examining the v$parameter view.