[20121023]改变show parameter的显示宽度.txt

[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