[20171106]修改show spparameter的显示宽度.txt
--//很多年前做的,修改show parameter的显示宽度.
--//链接: [20121023]改变show parameter的显示宽度.txt=>http://blog.itpub.net/267265/viewspace-746988/
--//今天发现show spparameter显示宽度也不是很合理,修改看看,并做一个记录:
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> show spparameter control_files
SID NAME TYPE VALUE
-------- ----------------------------- ---------------------------------------- ----------------------------
* control_files string /mnt/ramdisk/book/control01.
ctl
* control_files string /mnt/ramdisk/book/control02.
ctl
--//出现折行情况,显示不是很好.
2.做一个跟踪:
SYS@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SYS@book> show spparameter control_files
SID NAME TYPE VALUE
-------- ----------------------------- ---------------------------------------- ----------------------------
* control_files string /mnt/ramdisk/book/control01.
ctl
* control_files string /mnt/ramdisk/book/control02.
ctl
SYS@book> @ &r/10046off
Session altered.
--//检查转储文件可以发现实际执行如下:
=====================
PARSING IN CURSOR #139831052073784 len=248 dep=0 uid=0 oct=3 lid=0 tim=1509954996964120 hv=3485984188 ad='7c0b1308' sqlid='28vy07r7wgtdw'
SELECT SID SID_COL_PLUS_SHOW_SPPARAM, NAME NAME_COL_PLUS_SHOW_SPPARAM, TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_SPPARAM FROM V$SPPARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_SPPARAM,VALUE_COL_PLUS_SHOW_SPPARAM
END OF STMT
--//很明显VLUES对应的显示字段是VALUE_COL_PLUS_SHOW_SPPARAM.
SYS@book> column value_col_plus_show_spparam format a100
SYS@book> show spparameter control_files
SID NAME TYPE VALUE
-------- ----------------------------- ---------------------------------------- -------------------------------
* control_files string /mnt/ramdisk/book/control01.ctl
* control_files string /mnt/ramdisk/book/control02.ctl
--//OK,现在显示就不会出现折行情况了.修改$ORACLE_HOME/sqlplus/admin/glogin.sql.加入column value_col_plus_show_spparamformat a100这行就永久有效了.