让show parameter显示隐含参数(11G)

2016-05-14 18:05 abce 阅读(525) 评论(0) 编辑收藏举报

1.创建视图show_hidden_v$parameter

create or replace view show_hidden_v$parameter
  (INST_ID, NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE,
    ISMODIFIED , ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH)
    as
   select x.inst_id,
           x.indx + 1,
           ksppinm,
           ksppity,
           ksppstvl,
          ksppstdvl,
          ksppstdf,
          decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
          decode(bitand(ksppiflg / 65536, 3),
                 1,
                 'IMMEDIATE',
                 2,
                 'DEFERRED',
                 3,
                 'IMMEDIATE',
                 'FALSE'),
         decode(bitand(ksppiflg, 4),
                4,
                 'FALSE',
                 decode(bitand(ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')),
          decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
          decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'),
          decode(bitand(ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE'),
          ksppdesc,
          ksppstcmnt,
          ksppihash
     from x$ksppi x, x$ksppcv y
    where (x.indx = y.indx);

2.授权一下

SQL> grant select on show_hidden_v$parameter to scott;
SQL> conn scott/tiger
SQL> create synonym v$parameter for sys.show_hidden_v$parameter;
SQL> show parameter latch