[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.
--//以前写的用来查询隐含参数的脚本如下：
$ 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.
