히든 파라미터 조회 뷰 생성

V$PARAMETER 뷰와 V$PARAMETER_VALID_VALUES 뷰는 파라미터 관련 정보를 조회할 수 있는 유용한 뷰지만, 히든 파라미터를 조회할 수 없는 불편함이 있다. 히든 파라미터 조회할 수 있는 뷰를 생성해보자.

아래 쿼리는 SYS 사용자에 히든 파라미터를 조회할 수 있는 GU$PARAMETER 뷰를 생성한다.^[각주:1] 동적 성능 뷰와 동일하게 GV_$, V_$ 뷰를 생성하고, select_catalog_role 롤에 SELECT 권한을 부여한 후, GV$, V$로 공용 시노님을 생성했다. 주석으로 표시된 열은 12.1 버전부터 사용할 수 있다.

-- 1-1
CREATE OR REPLACE VIEW gu$parameter AS
SELECT a.inst_id   AS inst_id
     , a.indx + 1  AS num
     , a.ksppinm   AS name
     , a.ksppity   AS type
     , b.ksppstvl  AS value
     , b.ksppstdvl AS display_value
     , b.ksppstdfl AS default_value -- 12.1
     , b.ksppstdf  AS isdefault
     , DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') AS isses_modifiable
     , DECODE (BITAND (a.ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE') AS issys_modifiable
     , DECODE (BITAND (a.ksppiflg / 524288, 1), 1, 'TRUE', 'FALSE') AS ispdb_modifiable -- 12.1
     , DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')) AS isinstance_modifiable
     , DECODE (BITAND (b.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') AS ismodified
     , DECODE (BITAND (b.ksppstvf, 2), 2, 'TRUE', 'FALSE') AS isadjusted
     , DECODE (BITAND (a.ksppilrmflg / 64, 1), 1, 'TRUE', 'FALSE') AS isdeprecated
     , DECODE (BITAND (a.ksppilrmflg / 268435456, 1), 1, 'TRUE', 'FALSE') AS isbasic
     , a.ksppdesc   AS description
     , b.ksppstcmnt AS update_comment
     , a.ksppihash  AS hash
     , b.con_id     AS con_id -- 12.1
FROM   x$ksppi a
     , x$ksppcv b
 WHERE b.indx = a.indx;

-- 1-2
CREATE OR REPLACE VIEW gu_$parameter AS
SELECT * FROM gu$parameter;

CREATE OR REPLACE VIEW  u_$parameter AS
SELECT inst_id
     , num
     , name
     , type
     , value
     , display_value
     , default_value -- 12.1
     , isdefault
     , isses_modifiable
     , issys_modifiable
     , ispdb_modifiable -- 12.1
     , isinstance_modifiable
     , ismodified
     , isadjusted
     , isdeprecated
     , isbasic
     , description
     , update_comment
     , hash
     , con_id -- 12.1
  FROM gu$parameter
 WHERE inst_id = USERENV('INSTANCE');

-- 1-3
GRANT SELECT ON gu_$parameter TO select_catalog_role;
GRANT SELECT ON  u_$parameter TO select_catalog_role;

-- 1-4
CREATE OR REPLACE PUBLIC SYNONYM gu$parameter FOR gu_$parameter;
CREATE OR REPLACE PUBLIC SYNONYM  u$parameter FOR  u_$parameter;

아래 쿼리는 위 쿼리와 동일한 방식으로 GU$PARAMETER_VALID_VALUES 뷰를 생성한다.

-- 2-1
CREATE OR REPLACE VIEW gu$parameter_valid_values AS
SELECT inst_id                 AS inst_id
     , parno_kspvld_values     AS num
     , name_kspvld_values      AS name
     , ordinal_kspvld_values   AS ordinal
     , value_kspvld_values     AS value
     , isdefault_kspvld_values AS isdefault
     , con_id                  AS con_id -- 12.1
  FROM x$kspvld_values;

-- 2-2
CREATE OR REPLACE VIEW gu_$parameter_valid_values AS
SELECT * FROM gu$parameter_valid_values;

CREATE OR REPLACE VIEW  u_$parameter_valid_values AS
SELECT num
     , name
     , ordinal
     , value
     , isdefault
     , con_id -- 12.1
  FROM gu$parameter_valid_values
 WHERE inst_id = USERENV('INSTANCE');

-- 2-3
GRANT SELECT ON gu_$parameter_valid_values TO select_catalog_role;
GRANT SELECT ON  u_$parameter_valid_values TO select_catalog_role;

-- 2-4
CREATE OR REPLACE PUBLIC SYNONYM gu$parameter_valid_values FOR gu_$parameter_valid_values;
CREATE OR REPLACE PUBLIC SYNONYM  u$parameter_valid_values FOR  u_$parameter_valid_values;

일반 사용자도 생성한 뷰를 통해 히든 파라미터를 조회할 수 있다.

-- 3-1
SELECT name, value, default_value, description
  FROM u$parameter
 WHERE name = '_optimizer_or_expansion';

NAME                    VALUE DEFAULT_VALUE DESCRIPTION
----------------------- ----- ------------- ----------------------------------
_optimizer_or_expansion DEPTH depth         control or expansion approach used

1개의 행이 선택되었습니다.

-- 3-2
SELECT ordinal, value, isdefault
  FROM u$parameter_valid_values
 WHERE name = '_optimizer_or_expansion';

ORDINAL VALUE   ISDEFAULT
------- ------- ---------
      1 DEPTH   FALSE
      2 BREADTH FALSE

2 행이 선택되었습니다.

1. SYS 사용자에 사용자 오브젝트를 생성하는 것은 바람직하지 못하다. 테스트 환경에서만 활용하도록 하자. [본문으로]