Oracle如何更改show parameter顯示內容的列的格式

PiscesCanon發表於2017-05-26
Oracle

剛好今天看到有人在群裡問了一個問題，自己剛好有點興趣。
以下是資料庫的測試版本：

點選(此處)摺疊或開啟

  1. SYS@proc> select * from v$version where rownum=1;

  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

  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';

  3. Session altered.

  5. SYS@proc> show parameter control_files

  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';

  14. Session altered.
  15.  
  16. SYS@proc> select value from v$diag_info where name like '%De%';

  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

  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

  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;

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章