【Oracle】初期化パラメータ完全ガイド｜SPFILE・PFILE の違い・SHOW PARAMETER・ALTER SYSTEM・V$PARAMETER まで解説

-- SPFILE が使われている場合は VALUE に SPFILE のパスが表示される
-- PFILE の場合は VALUE が空（NULL）になる
SHOW PARAMETER spfile;

-- 別の確認方法: V$PARAMETER で spfile_name を確認
SELECT name, value FROM V$PARAMETER WHERE name = 'spfile';
-- VALUE が空 = PFILE 起動
-- VALUE にパスが入っている = SPFILE 起動

-- パラメータ名の一部を指定して前方一致・部分一致で検索
SHOW PARAMETER sga         -- SGA 関連パラメータ一覧
SHOW PARAMETER db_cache    -- db_cache_size を確認
SHOW PARAMETER optimizer   -- オプティマイザ関連パラメータ

-- 特定のパラメータを正確に確認
SHOW PARAMETER db_block_size

-- パラメータの詳細情報を確認
SELECT
    name,
    value,                   -- 現在有効な値
    display_value,           -- 見やすい形式の値（例: 4G など）
    type,                    -- 1=BOOLEAN, 2=STRING, 3=INTEGER, 4=PARAMETER_FILE, 5=RESERVED, 6=BIG_INTEGER
    issys_modifiable,        -- ALTER SYSTEM で変更可能か（IMMEDIATE/DEFERRED/FALSE）
    isinstance_modifiable,   -- 現在の起動中に変更可能か
    isses_modifiable,        -- ALTER SESSION で変更可能か
    ismodified,              -- 現在の起動中に変更されたか（MODIFIED/FALSE）
    description
FROM V$PARAMETER
WHERE name LIKE '%memory%'   -- メモリ関連パラメータを検索
ORDER BY name;

-- V$SPPARAMETER は SPFILE の内容を直接参照する
-- V$PARAMETER（実行中の値）と比較できる
SELECT
    name,
    value AS spfile_value,      -- SPFILE に記録されている値
    display_value,
    isspecified                 -- SPFILE に明示的に記録されているか（TRUE/FALSE）
FROM V$SPPARAMETER
WHERE isspecified = TRUE        -- 明示的に設定されたパラメータのみ
ORDER BY name;

-- V$PARAMETER と V$SPPARAMETER を比較（再起動後に反映される変更を確認）
SELECT
    p.name,
    p.value        AS current_value,    -- 現在の値
    sp.value       AS spfile_value      -- SPFILE に保存されている値（再起動後に適用）
FROM V$PARAMETER p
JOIN V$SPPARAMETER sp ON p.name = sp.name
WHERE sp.isspecified = TRUE
  AND p.value != sp.value              -- 値が異なるもの（再起動後に変わる）
ORDER BY p.name;

-- 即時に変更して SPFILE にも永続化する（BOTH = デフォルト）
ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target = 2G;   -- SCOPE 省略時は BOTH

-- メモリのみ変更（テスト・一時的な変更）
ALTER SYSTEM SET optimizer_mode = FIRST_ROWS_10 SCOPE=MEMORY;

-- SPFILE のみ変更（静的パラメータ、再起動が必要なもの）
ALTER SYSTEM SET db_block_size = 8192 SCOPE=SPFILE;
-- 静的パラメータを MEMORY や BOTH に変更しようとするとエラーになる
-- ORA-02095: 指定された初期化パラメータは変更できません

-- パラメータをデフォルト値に戻す
ALTER SYSTEM RESET optimizer_mode SCOPE=BOTH;

-- issys_modifiable = FALSE のパラメータは ALTER SYSTEM で変更できない（SPFILE のみ可）
SELECT name, value, display_value, issys_modifiable
FROM V$PARAMETER
WHERE name IN (
    'db_block_size',       -- FALSE: 静的（変更不可）
    'sga_target',          -- IMMEDIATE: 即時変更可能
    'open_cursors',        -- IMMEDIATE: 即時変更可能
    'pga_aggregate_target' -- IMMEDIATE: 即時変更可能
)
ORDER BY name;
-- issys_modifiable:
--   FALSE    = 静的パラメータ（SCOPE=SPFILE のみ、再起動後に反映）
--   IMMEDIATE = 即時変更可能（SCOPE=MEMORY/BOTH が使える）
--   DEFERRED  = 変更は次のセッション接続時から有効

-- SPFILE の内容をテキスト形式の PFILE として出力（DBA権限必要）
-- SYS ユーザーで実行する
CREATE PFILE = '/home/oracle/init_backup.ora' FROM SPFILE;

-- ファイルパスを省略するとデフォルトの場所（$ORACLE_HOME/dbs/init{SID}.ora）に作成
CREATE PFILE FROM SPFILE;

-- PFILE（テキスト）から SPFILE（バイナリ）を作成する
-- DB が起動していない状態（NOMOUNT）またはファイルを直接指定して実行
CREATE SPFILE = '/home/oracle/spfile_new.ora' FROM PFILE = '/home/oracle/init.ora';

-- デフォルトのパスに作成
CREATE SPFILE FROM PFILE;