【Oracle】ORA-08103の原因と解決方法｜object no longer exists・実行中に表が消えた時の対処

-- セッションA: 長いSELECTを実行中
SELECT *
FROM sales_work
WHERE sales_date >= DATE '2026-01-01';

-- セッションB: 同じ表をTRUNCATE
TRUNCATE TABLE sales_work;

-- セッションA側で ORA-08103 が発生することがある

-- セッションA: 特定パーティションを含む集計中
SELECT SUM(amount)
FROM sales
WHERE sales_date >= DATE '2026-05-01'
  AND sales_date <  DATE '2026-06-01';

-- セッションB: 対象パーティションを入れ替え
ALTER TABLE sales
  EXCHANGE PARTITION p202605
  WITH TABLE sales_202605_new
  WITHOUT VALIDATION;

DROP TABLE report_work PURGE;

CREATE TABLE report_work AS
SELECT *
FROM report_source;

-- 名前は同じでも、内部的には別オブジェクトになる

SELECT owner,
       object_name,
       object_type,
       object_id,
       data_object_id,
       status,
       created,
       last_ddl_time
FROM all_objects
WHERE owner = 'APP'
  AND object_name IN ('SALES_WORK', 'SALES')
ORDER BY owner, object_name, object_type;

-- DDL前に控えておく、または監査ログと突き合わせる
SELECT object_name,
       object_id,
       data_object_id,
       last_ddl_time
FROM user_objects
WHERE object_name = 'SALES_WORK'
  AND object_type = 'TABLE';

TRUNCATE TABLE sales_work;

-- TRUNCATE後はDATA_OBJECT_IDやLAST_DDL_TIMEが変わることがある
SELECT object_name,
       object_id,
       data_object_id,
       last_ddl_time
FROM user_objects
WHERE object_name = 'SALES_WORK'
  AND object_type = 'TABLE';

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

SELECT object_name,
       subobject_name,
       object_type,
       object_id,
       data_object_id,
       last_ddl_time
FROM user_objects
WHERE object_name = 'SALES'
ORDER BY subobject_name NULLS FIRST;

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       sql_text
FROM unified_audit_trail
WHERE event_timestamp >= SYSTIMESTAMP - INTERVAL '1' DAY
  AND action_name IN (
    'DROP TABLE',
    'TRUNCATE TABLE',
    'ALTER TABLE',
    'CREATE TABLE',
    'RENAME'
  )
ORDER BY event_timestamp DESC;

-- AWRに残っているDDLらしきSQLを探す
SELECT sql_id,
       parsing_schema_name,
       sql_text
FROM dba_hist_sqltext
WHERE UPPER(sql_text) LIKE 'TRUNCATE TABLE%'
   OR UPPER(sql_text) LIKE 'DROP TABLE%'
   OR UPPER(sql_text) LIKE 'ALTER TABLE%'
ORDER BY sql_id;

-- DBMS_SCHEDULERの実行履歴を見る
SELECT log_date,
       owner,
       job_name,
       status,
       additional_info
FROM dba_scheduler_job_run_details
WHERE log_date >= SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY log_date DESC;

SELECT object_name,
       original_name,
       type,
       droptime
FROM user_recyclebin
WHERE original_name IN ('SALES_WORK', 'REPORT_WORK')
ORDER BY droptime DESC;

-- 戻せる場合
FLASHBACK TABLE report_work TO BEFORE DROP;

-- メンテナンス前に対象表を参照しているセッションを確認
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 q.sql_text LIKE '%SALES%'
ORDER BY s.logon_time;

-- 避けたい例: 参照中の可能性がある本表をDROPして同名で作り直す
DROP TABLE sales_summary PURGE;

CREATE TABLE sales_summary AS
SELECT customer_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY customer_id;

-- 代替案: ステージング表を作成し、切り替えタイミングを短くする
CREATE TABLE sales_summary_new AS
SELECT customer_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY customer_id;

-- DROPならリサイクルビン確認
SELECT original_name,
       object_name,
       droptime
FROM user_recyclebin
WHERE original_name = 'SALES_WORK';

-- Flashback Queryで過去状態を参照できるか確認
SELECT COUNT(*)
FROM sales_work AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);