Oracle如何更改show parameter显示内容的列的格式

刚好今天看到有人在群里问了一个问题，自己刚好有点兴趣。
以下是数据库的测试版本：

点击(此处)折叠或打开

1. SYS@proc> select * from v$version where rownum=1;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

问题是如何让下边value的内容显示在一行上边。

点击(此处)折叠或打开

1. SYS@proc> show parameter control_files
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. control_files                        string      /u01/app/oracle/oradata/proc/c
   
6.                                                  ontrol01.ctl, /u01/app/oracle/
   
7.                                                  oradata/proc/control02.ctl

于是做了下10046：

点击(此处)折叠或打开

1. SYS@proc> alter session set events '10046 trace name context forever,level 12';
   
2. 
   
3. Session altered.
   
4. 
   
5. SYS@proc> show parameter control_files
   
6. 
   
7. NAME                                 TYPE        VALUE
   
8. ------------------------------------ ----------- ------------------------------
   
9. control_files                        string      /u01/app/oracle/oradata/proc/c
   
10.                                                  ontrol01.ctl, /u01/app/oracle/
    
11.                                                  oradata/proc/control02.ctl
    
12. SYS@proc> alter session set events '10046 trace name context off';
    
13. 
    
14. Session altered.
    
15.  
    
16. SYS@proc> select value from v$diag_info where name like '%De%';
    
17. 
    
18. VALUE
    
19. --------------------------------------------------------------
    
20. /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_ora_3282.trc

查看10046产生的trace，可以看到show parameter control_files实际上是执行了下边的sql语句：

点击(此处)折叠或打开

1. 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
   
2. FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ)
   
3. ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM
   
4. END OF STMT
   
5. PARSE #139976962042672:c=0,e=429,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1483720550329399
   
6. BINDS #139976962042672:
   
7.  Bind#0
   
8.   oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
   
9.   oacflg=20 fl2=1000000 frm=01 csi=852 siz=32 off=0
   
10.   kxsbbbfp=7f4eed1966e0 bln=32 avl=15 flg=05
    
11.   value="%control_files%"

所以，value列实际上就是 VALUE_COL_PLUS_SHOW_PARAM ，所以对该列col VALUE_COL_PLUS_SHOW_PARAM for a100即可。

点击(此处)折叠或打开

1. SYS@proc> show parameter control_files
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. control_files                        string      /u01/app/oracle/oradata/proc/c
   
6.                                                  ontrol01.ctl, /u01/app/oracle/
   
7.                                                  oradata/proc/control02.ctl
   
8. SYS@proc> col VALUE_COL_PLUS_SHOW_PARAM for a100
   
9. SYS@proc> show parameter control_files
   
10. 
    
11. NAME                                 TYPE        VALUE
    
12. ------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
    
13. control_files                        string      /u01/app/oracle/oradata/proc/control01.ctl, /u01/app/oracle/oradata/proc/control02.ctl

尝试执行该sql语句:

点击(此处)折叠或打开

1. SYS@proc> SELECT NAME NAME_COL_PLUS_SHOW_PARAM,
   
2.     DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',6,'big integer', 'unknown') TYPE,
   
3.     DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
   
4.     FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%control_files%') ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM;
   
5. 
   
6. NAME                                 TYPE        VALUE
   
7. ------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
   
8. control_files                        string      /u01/app/oracle/oradata/proc/control01.ctl, /u01/app/oracle/oradata/proc/control02.ctl

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30174570/viewspace-2139919/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30174570/viewspace-2139919/