[2025-10-26] 오라클 파라미터 조회 SQL문 (일반, 히든 한번에 조회)

■ [2025-10-26] 오라클 파라미터 조회 SQL문 (일반, 히든 한번에 조회)

 

1. 현재 인스턴스에 설정된 파라미터 조회 (일반, 히든 모두 조회)

 

SELECT *
  FROM
     (
        SELECT '일반' AS "구분"
             , A.NAME AS "PARAMETER"
             , A.VALUE AS "VALUE"
             , A.DISPLAY_VALUE AS "DISPLAY_VALUE"
             , A.DEFAULT_VALUE AS "DEFAULT_VALUE"
             , A.ISDEFAULT AS "IS_DEFALUT"
             , A.ISSES_MODIFIABLE AS "세션변경"
             , A.ISSYS_MODIFIABLE AS "시스템변경"
             , A.ISINSTANCE_MODIFIABLE AS "인스턴스변경"
             , A.ISPDB_MODIFIABLE AS "PDB변경"
             , A.DESCRIPTION AS "설명"
             , A.UPDATE_COMMENT AS "UPDATE_COMMENT"
          FROM V$PARAMETER A
          WHERE NOT EXISTS (SELECT 1 FROM SYS.X$KSPPI K WHERE K.KSPPINM = A.NAME AND K.KSPPINM LIKE '\_%' ESCAPE '\')
        UNION ALL
        SELECT '히든' AS "구분"
             , A.KSPPINM AS "PARAMETER"
             , B.KSPPSTVL AS "VALUE"    
             , B.KSPPSTDVL AS "DISPLAY_VALUE"
             , B.KSPPSTDFL AS "DEFAULT_VALUE"
             , B.KSPPSTDF AS "IS_DEFALUT"
             , DECODE (BITAND (A.KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE') AS "세션변경"
             , DECODE (BITAND (a.ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE') AS "시스템변경"
             , DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')) AS "인스턴스변경"
             , DECODE (BITAND (a.ksppiflg / 524288, 1), 1, 'TRUE', 'FALSE') AS "PDB변경" 
             , A.KSPPDESC AS "설명"
             , '' AS "UPDATE_COMMENT"
          FROM SYS.X$KSPPI A, SYS.X$KSPPCV B
         WHERE A.INDX = B.INDX
           AND A.KSPPINM LIKE '\_%' ESCAPE '\'
     ) A
 where UPPER(A.PARAMETER) LIKE '%' || UPPER('bct') || '%' --> 파라미터 검색
;

 

2. SPFILE에 등록된 파라미터의 내용까지 확인하는 방법 (일반, 히든 모두 조회)

--> 파라미터 변경 시 spfile에만 적용 한 후 아직 재기동 하지 않은 상황에서 유용하게 사용가능

 

SELECT *
  FROM
     (
        SELECT '일반' AS "구분"
             , A.NAME AS "PARAMETER"
             , A.VALUE AS "VALUE"
             , A.DISPLAY_VALUE AS "DISPLAY_VALUE"
             , A.DEFAULT_VALUE AS "DEFAULT_VALUE"
             , A.ISDEFAULT AS "IS_DEFALUT"
             , A.ISSES_MODIFIABLE AS "세션변경"
             , A.ISSYS_MODIFIABLE AS "시스템변경"
             , A.ISINSTANCE_MODIFIABLE AS "인스턴스변경"
             , A.ISPDB_MODIFIABLE AS "PDB변경"
             , A.DESCRIPTION AS "설명"
             , A.UPDATE_COMMENT AS "UPDATE_COMMENT"
          FROM V$PARAMETER A
          WHERE NOT EXISTS (SELECT 1 FROM X$KSPPI K WHERE K.KSPPINM = A.NAME AND K.KSPPINM LIKE '\_%' ESCAPE '\')
        UNION ALL
           SELECT 'SPFILE' AS "구분"
             , A.NAME AS "PARAMETER"
             , A.VALUE AS "VALUE"
             , A.DISPLAY_VALUE AS "DISPLAY_VALUE"
             , '' AS "DEFAULT_VALUE"
             , '' AS "IS_DEFALUT"
             , '' AS "세션변경"
             , '' AS "시스템변경"
             , '' AS "인스턴스변경"
             , '' AS "PDB변경"
             , '' AS "설명"
             , A.UPDATE_COMMENT AS "UPDATE_COMMENT"
          FROM V$SPPARAMETER A
        UNION ALL
        SELECT '히든' AS "구분"
             , A.KSPPINM AS "PARAMETER"
             , B.KSPPSTVL AS "VALUE"    
             , B.KSPPSTDVL AS "DISPLAY_VALUE"
             , B.KSPPSTDFL AS "DEFAULT_VALUE"
             , B.KSPPSTDF AS "IS_DEFALUT"
             , DECODE (BITAND (A.KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE') AS "세션변경"
             , DECODE (BITAND (a.ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE') AS "시스템변경"
             , DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (ksppiflg / 65536, 3), 0, 'FALSE', 'TRUE')) AS "인스턴스변경"
             , DECODE (BITAND (a.ksppiflg / 524288, 1), 1, 'TRUE', 'FALSE') AS "PDB변경" 
             , A.KSPPDESC AS "설명"
             , '' AS "UPDATE_COMMENT"
          FROM X$KSPPI A, X$KSPPCV B
         WHERE A.INDX = B.INDX
           AND A.KSPPINM LIKE '\_%' ESCAPE '\'
     ) A
 WHERE (    UPPER(A.PARAMETER) LIKE '%' || UPPER('bct') || '%' --> 파라미터 검색
       )
;