【Oracle】ORA-14400の原因と解決方法｜inserted partition key does not map to any partition

CREATE TABLE sales (
  sales_id   NUMBER,
  sales_date DATE NOT NULL,
  amount     NUMBER
)
PARTITION BY RANGE (sales_date) (
  PARTITION p202604 VALUES LESS THAN (DATE '2026-05-01'),
  PARTITION p202605 VALUES LESS THAN (DATE '2026-06-01')
);

-- 2026年6月分を受けるパーティションがないため ORA-14400
INSERT INTO sales (sales_id, sales_date, amount)
VALUES (1, DATE '2026-06-01', 1000);

CREATE TABLE customers_by_region (
  customer_id NUMBER,
  region_code VARCHAR2(10)
)
PARTITION BY LIST (region_code) (
  PARTITION p_jp VALUES ('JP'),
  PARTITION p_us VALUES ('US')
);

-- EUを受けるパーティションがないため ORA-14400
INSERT INTO customers_by_region (customer_id, region_code)
VALUES (100, 'EU');

-- p202605 は 2026-06-01 未満だけを受ける
PARTITION p202605 VALUES LESS THAN (DATE '2026-06-01')

-- これは入らない。次の p202606 が必要
DATE '2026-06-01'

SELECT name,
       object_type,
       column_name,
       column_position
FROM user_part_key_columns
WHERE name = 'SALES'
ORDER BY column_position;

SELECT table_name,
       partitioning_type,
       subpartitioning_type,
       partition_count
FROM user_part_tables
WHERE table_name = 'SALES';

SELECT partition_name,
       partition_position,
       high_value,
       tablespace_name,
       num_rows,
       last_analyzed
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position;

SELECT DBMS_METADATA.GET_DDL('TABLE', 'SALES')
FROM dual;

-- 指定日が入るパーティションを確認する
SELECT COUNT(*)
FROM sales PARTITION FOR (DATE '2026-06-15');

-- 対応パーティションがなければエラーになるため、投入前チェックに使える

SELECT MIN(sales_date) AS min_sales_date,
       MAX(sales_date) AS max_sales_date,
       COUNT(*)        AS row_count
FROM sales_load_work
WHERE batch_id = :batch_id;

CREATE TABLE partition_boundary_master (
  table_name      VARCHAR2(128) NOT NULL,
  partition_name  VARCHAR2(128) NOT NULL,
  lower_date      DATE,
  upper_date      DATE NOT NULL,
  CONSTRAINT pk_partition_boundary_master
    PRIMARY KEY (table_name, partition_name)
);

-- 投入データがどの管理境界にも入らない値を確認する
SELECT w.sales_date,
       COUNT(*) AS row_count
FROM sales_load_work w
WHERE w.batch_id = :batch_id
  AND NOT EXISTS (
    SELECT 1
    FROM partition_boundary_master b
    WHERE b.table_name = 'SALES'
      AND w.sales_date >= NVL(b.lower_date, DATE '0001-01-01')
      AND w.sales_date <  b.upper_date
  )
GROUP BY w.sales_date
ORDER BY w.sales_date;

CREATE TABLE region_partition_master (
  region_code VARCHAR2(10) PRIMARY KEY
);

INSERT INTO region_partition_master(region_code) VALUES ('JP');
INSERT INTO region_partition_master(region_code) VALUES ('US');

SELECT w.region_code,
       COUNT(*) AS row_count
FROM customers_load_work w
WHERE NOT EXISTS (
  SELECT 1
  FROM region_partition_master m
  WHERE m.region_code = w.region_code
)
GROUP BY w.region_code
ORDER BY w.region_code;

ALTER TABLE sales
  ADD PARTITION p202606
  VALUES LESS THAN (DATE '2026-07-01')
  TABLESPACE sales_ts;

CREATE TABLE sales (
  sales_id   NUMBER,
  sales_date DATE NOT NULL,
  amount     NUMBER
)
PARTITION BY RANGE (sales_date) (
  PARTITION p202605 VALUES LESS THAN (DATE '2026-06-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

-- pmaxを月次パーティションへ分割する例
ALTER TABLE sales
  SPLIT PARTITION pmax AT (DATE '2026-07-01')
  INTO (
    PARTITION p202606,
    PARTITION pmax
  );

CREATE TABLE sales (
  sales_id   NUMBER,
  sales_date DATE NOT NULL,
  amount     NUMBER
)
PARTITION BY RANGE (sales_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
  PARTITION p202601 VALUES LESS THAN (DATE '2026-02-01')
);

-- 2026年6月のデータをINSERTすると、必要なINTERVALパーティションが作成される
INSERT INTO sales (sales_id, sales_date, amount)
VALUES (1, DATE '2026-06-15', 1000);

ALTER TABLE customers_by_region
  ADD PARTITION p_eu VALUES ('EU');

-- 想定外値を受ける安全弁
ALTER TABLE customers_by_region
  ADD PARTITION p_other VALUES (DEFAULT);

-- ロード前に対象日付範囲を確認する
SELECT MIN(sales_date) AS min_date,
       MAX(sales_date) AS max_date,
       COUNT(*)        AS rows_to_load
FROM sales_load_work;

-- LISTパーティションなら未知コードを確認する
SELECT region_code,
       COUNT(*) AS row_count
FROM customers_load_work
GROUP BY region_code
ORDER BY region_code;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => USER,
    tabname     => 'SALES',
    partname    => 'P202606',
    granularity => 'PARTITION',
    cascade     => TRUE
  );
END;
/

-- 次月を受けるパーティションが存在するか確認する例
SELECT partition_name,
       high_value
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_position DESC
FETCH FIRST 3 ROWS ONLY;