Skip to content

SHOW PARAMETER, How and Why

  • by

What is SHOW PARAMETER?

SHOW PARAMETER is a SQL*Plus specific command that can show all parameters, types and their values in a simple way. If a string is added to the command, then it will search for parameters that contain the string, for example:

SQL> show parameter name;

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string       ORCLCDB
db_unique_name                       string       ORCLCDB
global_names                         boolean      FALSE
instance_name                        string       ORCLCDB
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
service_names                        string       ORCLCDB

As we can see, it lists all parameters which names contain name.

The Base Query

Actually, SHOW PARAMETER is a shorthand of querying V$PARAMETER by the following way.

SQL> column name format a36;
SQL> column type format a12;
SQL> column value format a30;
SQL> set feedback off;
SQL> select name, case type when 1 then 'boolean' when 2 then 'string' when 3 then 'integer' end type, value from v$parameter where name like '%name%' order by 1;

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
cdb_cluster_name                     string
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string       ORCLCDB
db_unique_name                       string       ORCLCDB
global_names                         boolean      FALSE
instance_name                        string       ORCLCDB
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
service_names                        string       ORCLCDB

As we can see, both results are the same.

Add Wild Cards

That's why we can add one or more wild cards % in the search string.

One wild card

SQL> show parameter db%name

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
cdb_cluster_name                     string
db_file_name_convert                 string
db_name                              string       ORCLCDB
db_unique_name                       string       ORCLCDB
pdb_file_name_convert                string

More wild cards

SQL> show parameter db%name%convert

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
db_file_name_convert                 string
pdb_file_name_convert                string

Always keep in mind, SHOW PARAMETER is a shorthand query basically. By the way, SHOW can abbreviated as SHO.

It's worth mentioning, SHOW PARAMETER command can be used in all releases of SQL*Plus, but NOT all SHOW commands in SQL*Plus can be used in all releases. For example, SHOW PDBS can only be used in 12c and later releases of SQL*Plus.

Leave a Reply

Your email address will not be published.