SHOW PARAMTER columns format in SQLPLUS

 

Why ?

In SQL-Plus utility you may have a values listed in short two lines and while you try the normal set linesize xxx or set pagesize xxx then the column name format axx with name , type or value column of the show parameter , it doesn’t affected , this is because these column names is differ from what you see in screen. Below is the default value in oracle sqlplus :

- Defaults for SHOW PARAMETERS
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

How ?

Below are commands in sqlplus to edit the name , type or value of show parameter for sga as example  :

1- to check the value of sga:

SQL> show parameter sga

NAME                           TYPE

------------------------------ ------------------------------

VALUE------------------------------

lock_sga                       boolean

FALSE

pre_page_sga                   boolean

FALSE

sga_max_size                   big integer

300M

sga_target                     big integer0

 2- To set the parameter column format as below (change the number as needed) :

SQL> column NAME_COL_PLUS_SHOW_PARAM format A20

SQL> column TYPE format A20

SQL> column VALUE_COL_PLUS_SHOW_PARAM format A20

 3- check the format layout after that :

SQL> show parameter sga

NAME                 TYPE                 VALUE

-------------------- -------------------- --------------------

lock_sga             boolean              FALSE

pre_page_sga         boolean              FALSE

sga_max_size         big integer          300M

sga_target           big integer          0