Oracle Spatial 臨時表限制突破：自訂會話級臨時表支援 LOB、主外鍵與全域存取

Oracle 內建臨時表 (GTT) 限制

自訂會話級臨時表設計

1. 表結構：加入 SESSION_ID 派欄位

CREATE TABLE tmp_spatial_work (
  session_id   NUMBER NOT NULL,           -- SYS_CONTEXT('USERENV','SID')
  task_id      NUMBER NOT NULL,           -- 業務主鍵
  geom         SDO_GEOMETRY,              -- 支援 LOB/空間型別
  attr_json    CLOB,
  create_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
  CONSTRAINT pk_tmp_spatial_work PRIMARY KEY (session_id, task_id)
);

-- 子表範例：支援外來鍵
CREATE TABLE tmp_spatial_detail (
  session_id   NUMBER NOT NULL,
  task_id      NUMBER NOT NULL,
  seq_no       NUMBER NOT NULL,
  geom         SDO_GEOMETRY,
  CONSTRAINT pk_tmp_spatial_detail PRIMARY KEY (session_id, task_id, seq_no),
  CONSTRAINT fk_tmp_spatial_detail_work
    FOREIGN KEY (session_id, task_id)
    REFERENCES tmp_spatial_work(session_id, task_id)
);

2. 登出觸發器：自動清理

CREATE OR REPLACE TRIGGER trg_cleanup_tmp_spatial
AFTER LOGOFF ON DATABASE
DECLARE
  v_sid NUMBER := SYS_CONTEXT('USERENV','SID');
BEGIN
  DELETE FROM tmp_spatial_detail WHERE session_id = v_sid;
  DELETE FROM tmp_spatial_work   WHERE session_id = v_sid;
EXCEPTION WHEN OTHERS THEN NULL; -- 避免觸發器錯誤影響登出
END;
/

3. 應用層寫入/讀取規約

-- 寫入
INSERT INTO tmp_spatial_work(session_id, task_id, geom, attr_json)
VALUES (SYS_CONTEXT('USERENV','SID'), :task_id, :geom, :attr_json);

-- 讀取 (僅看見自己 Session 的資料)
SELECT * FROM tmp_spatial_work
WHERE session_id = SYS_CONTEXT('USERENV','SID');

4. 視圖封裝：對應用透明

CREATE OR REPLACE VIEW v_tmp_spatial_work AS
SELECT task_id, geom, attr_json, create_time
FROM tmp_spatial_work
WHERE session_id = SYS_CONTEXT('USERENV','SID');

-- 視圖觸發器支援 DML
CREATE OR REPLACE TRIGGER trg_v_tmp_spatial_work_iud
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_tmp_spatial_work
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO tmp_spatial_work(session_id, task_id, geom, attr_json)
    VALUES (SYS_CONTEXT('USERENV','SID'), :NEW.task_id, :NEW.geom, :NEW.attr_json);
  ELSIF UPDATING THEN
    UPDATE tmp_spatial_work
    SET geom = :NEW.geom, attr_json = :NEW.attr_json
    WHERE session_id = SYS_CONTEXT('USERENV','SID') AND task_id = :OLD.task_id;
  ELSIF DELETING THEN
    DELETE FROM tmp_spatial_work
    WHERE session_id = SYS_CONTEXT('USERENV','SID') AND task_id = :OLD.task_id;
  END IF;
END;
/

功能擴展設計

優勢總結

1. 完整支援 SDO_GEOMETRY / LOB：空間索引、空間運算子皆可用。
2. 主外鍵完整性：多表關聯臨時資料同享會話隔離。
3. 高併發無鎖衝突：無 DDL，純 DML，Row Level Lock 粒度細。
4. 管理員可跨 Session 存取：滿足除錯、稽核需求。
5. 統計資訊隔離：避免共用統計導致計畫錯誤。

遷移建議

• 既有 GTT 應用：評估是否受限於 LOB/FK，若是則逐步遷移至自訂模式。
• 新專案：直接採用自訂模式，建立通用框架 (Base Table + View + Trigger + Cleanup Job)。
• 19c+ 可考慮 Private Temporary Tables (ORA$PTT_)：支援 LOB、FK、Session 隔離、自動清理，語法 CREATE PRIVATE TEMPORARY TABLE ORA$PTT_MY_TMP ...，為官方最終解決方案。