[20230903]完善hide.sql脚本2.txt

[20230903]完善hide.sql脚本2.txt --//以前写的用来查询隐含参数的脚本如下： $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_value format a22 select    a.ksppinm  name,    a.ksppdesc DESCRIPTION,    b.ksppstdf DEFAULT_VALUE,    b.ksppstvl SESSION_VALUE,    c.ksppstvl SYSTEM_VALUE,    DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,    DECODE        (           BITAND (a.ksppiflg / 65536, 3)          ,1, 'IMMEDIATE'          ,2, 'DEFERRED'          ,3, 'IMMEDIATE'          ,'FALSE'        ) ISSYS_MODIFIABLE from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx  and a.indx = c.indx  and lower(a.ksppinm) like lower('%&1%') escape '\' order by 1; --//参考链接：http://blog.itpub.net/267265/viewspace-2752521/=>[20210125]完善hide.sql脚本.txt --//一直存在一个小问题，假如查询如下： SYS@test> @ hide log_archive_dest_2 NAME                DESCRIPTION                          DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------- ------------------------------------ ------------- ------------- ------------ ----- --------- log_archive_dest_2  archival destination #2 text string  TRUE                                     TRUE  IMMEDIATE log_archive_dest_20 archival destination #20 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_21 archival destination #21 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_22 archival destination #22 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_23 archival destination #23 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_24 archival destination #24 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_25 archival destination #25 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_26 archival destination #26 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_27 archival destination #27 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_28 archival destination #28 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_29 archival destination #29 text string TRUE                                     TRUE  IMMEDIATE 11 rows selected. --//显示一堆自己不需要的查看的log_archive_dest_2X参数，以前遇到这类情况我仅仅粘贴log_archive_dest_2的结果。 --//而且要显示log_archive_dest_3参数,要另外执行@ hide log_archive_dest_3. --//最近优化项目时才想到使用正则表达式可以很好地规避这些缺点，改写如下： $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_value format a22 select    a.ksppinm  name,    a.ksppdesc DESCRIPTION,    b.ksppstdf DEFAULT_VALUE,    b.ksppstvl SESSION_VALUE,    c.ksppstvl SYSTEM_VALUE,    DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,    DECODE        (           BITAND (a.ksppiflg / 65536, 3)          ,1, 'IMMEDIATE'          ,2, 'DEFERRED'          ,3, 'IMMEDIATE'          ,'FALSE'        ) ISSYS_MODIFIABLE from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx  and a.indx = c.indx -- and lower(a.ksppinm) like lower('%&1%') --escape '\' and regexp_like (lower(a.ksppinm) ,lower('&1')) order by 1; --//这样就灵活许多，只要知道正则表达式的写法，很容易完成需要的显示结果。比如我需要显示 --//log_archive_dest_2,log_archive_dest_3参数,执行如下: SYS@test> @ hide log_archive_dest_[23]$ NAME               DESCRIPTION                         DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------ ----------------------------------- ------------- ------------- ------------ ----- --------- log_archive_dest_2 archival destination #2 text string TRUE                                     TRUE  IMMEDIATE log_archive_dest_3 archival destination #3 text string TRUE                                     TRUE  IMMEDIATE --//而且里面_不再像以前的like那样解析为任意字符。以前要显示包含"_ash_"字符串的参数,以前要执行@ hide \_ash\_ --//现在只要知道正则表达式的语法，就可以很容易实现满足自己需要的查询结果。 --//例子： @ hide _ash_ @ hide ^_ash_ @ hide log_archive_dest_[[:digit:]] @ hide log_archive_dest_[[:digit:]]{1}$ --//注:输出结果我不再贴出,大家可以自行测试.为了保留原来的执行文件,我把新建立的执行脚本命名hidez.sql. --//顺便贴上一些正则表达式的解析,摘自man grep文档,许多自己不经常使用,做一个记录. Character Classes and Bracket Expressions A  bracket expression is a list of characters enclosed by [ and ].  It matches any single character in that list.  If the first character of the list is the caret ^ then it matches any character not in the list;it is unspecified whether it matches an encoding error.  For example, the regular expression [0123456789] matches any single digit. Within a bracket expression, a range expression consists of two characters separated by a hyphen.  It matches any single character that sorts between the two characters, inclusive, using the  locale's  collating sequence  and  character set.   For  example, in the default C locale, [a-d] is equivalent to [abcd].  Many locales sort characters in dictionary order, and in these locales [a-d] is typically not equivalent to [abcd]; it might be equivalent to [aBbCcDd], for example.  To obtain the traditional interpretation of bracket expressions, you can use the C locale by setting the LC_ALL environment variable to the value C. Finally, certain named classes of characters are predefined within bracket expressions, as follows.  Their names are self  explanatory,  and  they  are  [:alnum:],  [:alpha:],  [:blank:],  [:cntrl:],  [:digit:], [:graph:],  [:lower:], [:print:],  [:punct:],  [:space:],  [:upper:], and [:xdigit:].  For example, [[:alnum:]] means the character class of numbers and letters in the current locale.  In the C locale and ASCII character set encoding, this is the same as [0-9A-Za-z].  (Note that the brackets in these class names are part of the symbolic names, and must be included in addition to  the  brackets  delimiting  the  bracket expression.)   Most  meta-characters  lose  their  special  meaning inside bracket expressions.  To include a literal ] place it first in the list.  Similarly, to include a literal ^ place it anywhere but first. Finally, to include a literal - place it last. Anchoring The caret ^ and the dollar sign $ are meta-characters that respectively match the empty string at the beginning and end of a line. The Backslash Character and Special Expressions The symbols \< and \> respectively match the empty string at the beginning and end of a word.  The symbol \b matches the empty string at the edge of a word, and \B matches the empty string provided it's  not  at  the edge of a word.  The symbol \w is a synonym for [_[:alnum:]] and \W is a synonym for [^_[:alnum:]]. Repetition A regular expression may be followed by one of several repetition operators: ?      The preceding item is optional and matched at most once. *      The preceding item will be matched zero or more times. +      The preceding item will be matched one or more times. {n}    The preceding item is matched exactly n times. {n,}   The preceding item is matched n or more times. {,m}   The preceding item is matched at most m times.  This is a GNU extension. {n,m}  The preceding item is matched at least n times, but not more than m times.