히든 파라미터 조회 뷰를 생성해보겠습니다. 보안상의 이유로 이 뷰는 운영 환경이 아닌 테스트 환경에서만 사용해야 합니다.
아래 쿼리로 기본이 되는 GV$PARAMETER 뷰와 GV$PARAMETER_VALID_VALUES 뷰의 소스를 조회할 수 있습니다.
-- 1
SELECT view_name, view_definition
FROM v$fixed_view_definition
WHERE view_name IN ('GV$PARAMETER', 'GV$PARAMETER_VALID_VALUES');
VIEW_NAME VIEW_DEFINITION
------------------------- -------------------------------------------------------------------------------------------------------
GV$PARAMETER select /*+ use_hash(x y) */ x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdfl, ksp...
GV$PARAMETER_VALID_VALUES SELECT INST_ID, PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES,...
2 행이 선택되었습니다.
아래와 같이 gu_$parameter, u_$parameter 뷰를 생성하겠습니다. 아울러 뷰에 대한 SELECT 권한을 select_catalog_role 롤에 부여하고 gu$parameter, u$parameter 시너님을 생성합니다.
-- 2-1
CREATE OR REPLACE VIEW gu_$parameter
AS
SELECT *
FROM TABLE (gv$ (CURSOR (
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
, 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
, DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (a.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
FROM x$ksppi a
, x$ksppcv b
WHERE b.indx = a.indx)));
-- 2-2
CREATE OR REPLACE VIEW u_$parameter
AS
SELECT 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
, 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
, DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (a.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
FROM x$ksppi a
, x$ksppcv b
WHERE b.indx = a.indx;
-- 2-3
GRANT SELECT ON gu_$parameter TO select_catalog_role;
GRANT SELECT ON u_$parameter TO select_catalog_role;
-- 2-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, u_$parameter_valid_values 뷰를 생성하겠습니다. 역시 뷰에 대한 SELECT 권한을 select_catalog_role 롤에 부여하고 gu$parameter_valid_values, u$parameter_valid_values 시너님을 생성니다.
-- 3-1
CREATE OR REPLACE VIEW gu_$parameter_valid_values
AS
SELECT *
FROM TABLE (gv$ (CURSOR (
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
FROM x$kspvld_values)));
-- 3-2
CREATE OR REPLACE VIEW u_$parameter_valid_values
AS
SELECT 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
FROM x$kspvld_values;
-- 3-3
GRANT SELECT ON gu_$parameter_valid_values TO select_catalog_role;
GRANT SELECT ON u_$parameter_valid_values TO select_catalog_role;
-- 3-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;
아래 쿼리는 gu$parameter 뷰와 u$parameter_valid_values 뷰에서 _optimizer_or_expansion 파라미터를 조회합니다.
-- 4-1
SELECT inst_id
, num
, name
, value
, default_value
, description
FROM gu$parameter
WHERE name = '_optimizer_or_expansion';
INST_ID NUM NAME VALUE DEFAULT_VALUE DESCRIPTION
------- ---- ----------------------- ----- ------------- ----------------------------------
1 4072 _optimizer_or_expansion DEPTH depth control or expansion approach used
2 4072 _optimizer_or_expansion DEPTH depth control or expansion approach used
2 행이 선택되었습니다.
-- 4-2
SELECT num
, name
, ordinal
, value
, isdefault
FROM u$parameter_valid_values
WHERE name = '_optimizer_or_expansion';
NUM NAME ORDINAL VALUE ISDEFAULT
---- ----------------------- ------- ------- ---------
4072 _optimizer_or_expansion 1 DEPTH FALSE
4072 _optimizer_or_expansion 2 BREADTH FALSE
2 행이 선택되었습니다.
[2024-07-28]
CDB 환경을 위해 a.con_id(x$ksppi)를 b.con_id(x$ksppcv)로 변경했습니다.