【Oracle】ORA-12838の原因と解決方法｜並列DML・APPEND後に同じ表を読めない時の対処

-- 並列DMLを明示していなくても、APPEND後に同じ表を読むと問題になることがある
INSERT /*+ APPEND */ INTO load_work
SELECT *
FROM load_source;

SELECT COUNT(*)
FROM load_work; -- ORA-12838の可能性

ROLLBACK;

ALTER SESSION ENABLE PARALLEL DML;

DELETE /*+ PARALLEL(load_work 4) */ FROM load_work
WHERE batch_id = :batch_id;

SELECT COUNT(*)
FROM load_work; -- ORA-12838の可能性

ROLLBACK;
ALTER SESSION DISABLE PARALLEL DML;

INSERT /*+ APPEND */ INTO sales_work
SELECT *
FROM sales_source
WHERE sales_date >= DATE '2026-01-01';

-- 同じトランザクション内で同じ表を読むため ORA-12838
SELECT COUNT(*)
FROM sales_work;

ALTER SESSION ENABLE PARALLEL DML;

UPDATE /*+ PARALLEL(sales_work 4) */ sales_work
SET load_status = 'DONE'
WHERE load_status = 'NEW';

-- 同じ表を再度変更するため ORA-12838
UPDATE sales_work
SET checked_flag = 'Y'
WHERE load_status = 'DONE';

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ PARALLEL(t 4) */ INTO customer_summary t
USING customer_summary_stage s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET t.total_amount = s.total_amount
WHEN NOT MATCHED THEN
  INSERT (customer_id, total_amount)
  VALUES (s.customer_id, s.total_amount);

-- 同じトランザクション内で対象表を読むと ORA-12838
SELECT COUNT(*)
FROM customer_summary;

INSERT /*+ APPEND */ INTO sales_work
SELECT *
FROM sales_source
WHERE sales_date >= DATE '2026-01-01';

COMMIT;

-- COMMIT後なら読める
SELECT COUNT(*)
FROM sales_work;

-- Step 1: 高速ロード
INSERT /*+ APPEND */ INTO sales_stage
SELECT *
FROM external_sales;

COMMIT;

-- Step 2: 件数確認
SELECT COUNT(*)
FROM sales_stage;

-- Step 3: 本表へ反映
MERGE INTO sales t
USING sales_stage s
ON (t.sales_id = s.sales_id)
WHEN MATCHED THEN
  UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN
  INSERT (sales_id, amount)
  VALUES (s.sales_id, s.amount);

COMMIT;

-- APPENDを使わない通常INSERT
INSERT INTO sales_work
SELECT *
FROM sales_source
WHERE sales_date >= DATE '2026-01-01';

-- 同じトランザクション内で確認できる
SELECT COUNT(*)
FROM sales_work;

-- 条件に応じてCOMMIT/ROLLBACKを選べる
COMMIT;

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(sales_stage 4) APPEND */ INTO sales_stage
SELECT /*+ PARALLEL(src 4) */ *
FROM sales_source src;

COMMIT;

ALTER SESSION DISABLE PARALLEL DML;

BEGIN
  INSERT /*+ APPEND */ INTO sales_stage
  SELECT *
  FROM sales_source
  WHERE batch_id = :batch_id;

  COMMIT;

  -- COMMIT後に検証する
  validate_sales_stage(p_batch_id => :batch_id);

  merge_sales(p_batch_id => :batch_id);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

-- NG: APPEND直後に同じ表を件数確認する
INSERT /*+ APPEND */ INTO import_stage
SELECT *
FROM import_source
WHERE batch_id = :batch_id;

-- SELECT COUNT(*) FROM import_stage; -- ここでORA-12838

-- OK: COMMIT後に確認する
COMMIT;

SELECT COUNT(*)
FROM import_stage
WHERE batch_id = :batch_id;

-- 高速ロード専用の表
INSERT /*+ APPEND */ INTO import_stage_raw
SELECT *
FROM import_source
WHERE batch_id = :batch_id;

COMMIT;

-- 検証済みデータを別表へ移す
INSERT INTO import_stage_valid
SELECT *
FROM import_stage_raw
WHERE batch_id = :batch_id
  AND required_col IS NOT NULL;

COMMIT;

SELECT sql_id,
       parsing_schema_name,
       executions,
       sql_text
FROM v$sql
WHERE UPPER(sql_text) LIKE '%APPEND%'
   OR UPPER(sql_text) LIKE '%PARALLEL%'
   OR UPPER(sql_text) LIKE '%ENABLE PARALLEL DML%'
ORDER BY last_active_time DESC
FETCH FIRST 50 ROWS ONLY;

SELECT s.sid,
       s.serial#,
       s.username,
       s.module,
       s.action,
       s.sql_id,
       q.sql_text
FROM v$session s
LEFT JOIN v$sql q
  ON q.sql_id = s.sql_id
WHERE s.username IS NOT NULL
ORDER BY s.logon_time DESC;