Oracle format the output of show parameter command

There are times when you need to show value column with some better formatting. This will not work if you issue

col value for a180

In order to get it working you need to set up value_col_plus_show_param as

SQL> col value_col_plus_show_param for a180
SQL>

Some important show commands:

show parameter show recyclebin show sga show spparameter (jonathanlewis)

SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      (ADDRESS_LIST=(ADDRESS=(PROTOC
                                                 OL=TCPS)(HOST=10.2.3.7)(PORT=
                                                 1523))(ADDRESS=(PROTOCOL=TCPS)
                                                 (HOST=10.2.3.8)(PORT=1523))(A
                                                 DDRESS=(PROTOCOL=TCPS)(HOST=10
                                                 .2.3.9)(PORT=1523)))
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
SQL>
SQL>
SQL>
SQL> col value_col_plus_show_param for a180
SQL>
SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.2.3.7)(PORT=1523))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.2.3.8)(PORT=1523))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.2.3.9)(PORT=1523)))
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
SQL>
SQL>
SQL>
SQL>
SQL> col value_col_plus_show_param for a80
SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------------------------------------
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=10.2.3.7)(PORT=1523))(ADDRESS=(PROT
                                                 OCOL=TCPS)(HOST=10.2.3.8)(PORT=1523))(ADDRESS=(PROTOCOL=TCPS)(HOST=10.2.3.9)(P
                                                 ORT=1523)))
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE
result_cache_remote_expiration       integer     0
SQL>


In case you want to see all of the current formatting set on sql type column and hit enter it will list all of the column formats.

SQL> column
COLUMN   NAME_COL_PLUS_PDB_RESTRICTED ON
HEADING  'RESTRICTED'
FORMAT   A10

COLUMN   NAME_COL_PLUS_PDB_OPEN_MODE ON
HEADING  'OPEN MODE'
FORMAT   A10

COLUMN   NAME_COL_PLUS_PDB_NAME ON
HEADING  'CON_NAME'
FORMAT   A30
word_wrap

COLUMN   NAME_COL_PLUS_PDB_CON_ID ON
HEADING  'CON_ID'

COLUMN   NAME_COL_PLUS_PDB_CONTAINERID ON
HEADING  'CON_ID'
FORMAT   a30
word_wrap

COLUMN   NAME_COL_PLUS_PDB_CONTAINER ON
HEADING  'CON_NAME'
FORMAT   a30
word_wrap

COLUMN   NAME_COL_PLUS_SHOW_EDITION ON
HEADING  'EDITION'
FORMAT   a30
word_wrap

COLUMN   result_plus_xquery ON
HEADING  'Result Sequence'

COLUMN   other_plus_exp ON
FORMAT   a44

COLUMN   other_tag_plus_exp ON
FORMAT   a29

COLUMN   object_node_plus_exp ON
FORMAT   a8

COLUMN   plan_plus_exp ON
FORMAT   a60

COLUMN   parent_id_plus_exp ON
HEADING  'p'
FORMAT   990

COLUMN   id_plus_exp ON
HEADING  'i'
FORMAT   990

COLUMN   droptime_plus_show_recyc ON
HEADING  'DROP TIME'
FORMAT   a19

COLUMN   objtype_plus_show_recyc ON
HEADING  'OBJECT TYPE'
FORMAT   a12

COLUMN   objectname_plus_show_recyc ON
HEADING  'RECYCLEBIN NAME'
FORMAT   a30

COLUMN   origname_plus_show_recyc ON
HEADING  'ORIGINAL NAME'
FORMAT   a16

COLUMN   SID_COL_PLUS_SHOW_SPPARAM ON
HEADING  'SID'
FORMAT   a8
word_wrap

COLUMN   VALUE_COL_PLUS_SHOW_SPPARAM ON
HEADING  'VALUE'
FORMAT   a28
word_wrap

COLUMN   NAME_COL_PLUS_SHOW_SPPARAM ON
HEADING  'NAME'
FORMAT   a29
word_wrap

COLUMN   value_col_plus_show_param ON
HEADING  'VALUE'
FORMAT   a80

COLUMN   name_col_plus_show_param ON
HEADING  'NAME'
FORMAT   a36

COLUMN   units_col_plus_show_sga ON
FORMAT   a15

COLUMN   name_col_plus_show_sga ON
FORMAT   a24

COLUMN   ERROR ON
FORMAT   A65
word_wrap

COLUMN   LINE/COL ON
FORMAT   A8

COLUMN   ROWLABEL ON
FORMAT   A15
SQL>