[20121023]改变show parameter的显示宽度.txt
当登录sqlplus 执行:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -------------------------- ------------------------------------------------------------
control_files string /u01/app/oracle11g/oradata/test/control01.ctl, /u01/app/orac
le11g/oradata/test/control02.ctl
--很明显value出现了折行现象,但是如何修改它的显示宽度呢?
--使用toad自带sqlmonitor(新版本改名叫SQL Tracker),不行使用10046跟踪应该也可以.可以发现实际执行的是:
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer',
'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER
BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
:NMBIND_SHOW_OBJ = '%control_files%'
--实际上的VALUE对应的就是VALUE_COL_PLUS_SHOW_PARAM.
SQL> column VALUE_COL_PLUS_SHOW_PARAM format a100
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
control_files string /u01/app/oracle11g/oradata/test/control01.ctl, /u01/app/oracle11g/oradata/test/control02.ctl
--这样就能够显示在一行里面了.
--如果想一直保持这个宽度,可以把column VALUE_COL_PLUS_SHOW_PARAM format a100写入$ORACLE_HOME/sqlplus/admin/glogin.sql文件中.
同样
SQL> show spparameter control_files
SID NAME TYPE VALUE
-------- ----------------------------- -------------------------- ----------------------------
* control_files string /u01/app/oracle11g/oradata/t
est/control01.ctl
* control_files string /u01/app/oracle11g/oradata/t
est/control02.ctl
--执行如下:
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
:NMBIND_SHOW_OBJ = '%control_files%'
column VALUE_COL_PLUS_SHOW_SPPARAM format a60
SQL> column VALUE_COL_PLUS_SHOW_SPPARAM format a60
SQL> show spparameter control_files
SID NAME TYPE VALUE
-------- ----------------------------- -------------------------- ------------------------------------------------------------
* control_files string /u01/app/oracle11g/oradata/test/control01.ctl
* control_files string /u01/app/oracle11g/oradata/test/control02.ctl
--同样的像其他show命令,都可以定位,像show recyclebin,show sga等.
--再举一个例子,我们生产系统内存很大,显示
SQL> show sga
Total System Global Area 1.2885E+10 bytes
Fixed Size 2105920 bytes
Variable Size 1660947904 bytes
Database Buffers 1.1207E+10 bytes
Redo Buffers 14667776 bytes
--很明显这样显示不是很好看(有些人认为可以^_^),跟踪发现实际执行的是如下语句:
SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','')
units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','')
units_col_plus_show_sga FROM V$SGA
SQL> column SUM(VALUE) format 99999999999999999
SQL> show sga
Total System Global Area 12884901888 bytes
Fixed Size 2105920 bytes
Variable Size 1660947904 bytes
Database Buffers 11207180288 bytes
Redo Buffers 14667776 bytes