Search

Top 60 Oracle Blogs

Recent comments

Show parameter

Just a quick little tip for Friday afternoon.

If you use the “show parameter” or “show spparameter” commands from SQL*Plus you’ve probably noticed that the parameter value may be too long for the defined output column, and even the parameter name may occasionally be too long. For example (from 12.2.0.1):

SQL> show spparameter optimizer

SID	 NAME			       TYPE	   VALUE
-------- ----------------------------- ----------- ----------------------------
*	 optimizer_adaptive_plans      boolean
*	 optimizer_adaptive_reporting_ boolean
	 only
*	 optimizer_adaptive_statistics boolean
*	 optimizer_capture_sql_plan_ba boolean
	 selines
*	 optimizer_dynamic_sampling    integer
*	 optimizer_features_enable     string
*	 optimizer_index_caching       integer
*	 optimizer_index_cost_adj      integer
*	 optimizer_inmemory_aware      boolean
*	 optimizer_mode                string
*	 optimizer_secure_view_merging boolean
*	 optimizer_use_invisible_index boolean
	 es
*	 optimizer_use_pending_statist boolean
	 ics
*	 optimizer_use_sql_plan_baseli boolean
	 nes

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/orcl12c/
                                                 adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      NONE
unified_audit_sga_queue_size         integer     1048576

The column definitions for these displays are embedded in the the SQL*Plus library code ($ORACLE_HOME/lib/libsqlplus.so), and the effects can be seen by spooling the output from the basic “column” command from SQL*Plus and searching for the word ‘param’ (ignoring case). The relevant column definitions are:


COLUMN   SID_COL_PLUS_SHOW_SPPARAM      ON      FORMAT    a8   HEADING SID
COLUMN   VALUE_COL_PLUS_SHOW_SPPARAM    ON      FORMAT   a28   HEADING VALUE
COLUMN   NAME_COL_PLUS_SHOW_SPPARAM     ON      FORMAT   a29   HEADING NAME

COLUMN   value_col_plus_show_param      ON      FORMAT   a30   HEADING VALUE
COLUMN   name_col_plus_show_param       ON      FORMAT   a36   HEADING NAME

If you want to change a few of the lengths (or even the column headings) you can simply add modified copies of these commands to the glogin script ($ORACLE_HOME/sqlplus/admin/glogin.sql) or to a login.sql script that’s referenced in your defined SQL path (and make sure you take a look at Franck Pachot’s blog to see how that has changed in 12.2).