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.