Oracle全局临时表与原表结构不同步解决方案咨询

以下回答基于你给出的全局临时表语法（Oracle 数据库特性）给出：

问题1：是否可创建数据库触发器，在原表结构发生变更时自动重建临时表？ #

技术上可以实现，但生产环境强烈不推荐。
你可以创建schema级别的DDL触发器，监听原表的ALTER、DROP、CREATE事件，触发后执行临时表重建逻辑，但存在几个无法规避的硬伤：

• DDL触发器触发时，原表的结构变更事务尚未提交，此时执行临时表的重建操作会直接争抢原表的元数据锁，轻则导致第三方厂商的表变更操作超时失败，重则触发数据库死锁。
• 如果触发器触发时刚好有存储过程在使用临时表执行DQ校验，会话持有的临时表锁会直接阻塞重建操作，反过来卡住原表的DDL流程，最终导致两边业务全部中断。
• 触发器的重建逻辑没有兜底判断，如果第三方厂商删除了你DQ校验依赖的核心字段，重建后的临时表会直接缺失对应字段，后续校验流程会无预兆报错。

问题2：若触发器方案不可行，是否存在其他动态重建临时表的方案？ #

有三个经过生产验证的方案，按推荐优先级排序：

• 存储过程前置惰性检查（最高优先级）
  不需要改动任何第三方流程，只需要在你现有DQ存储过程的最开头增加一段结构校验逻辑：
  1. 查询ALL_TAB_COLUMNS系统视图，对比原表和临时表的字段数量、字段名称、字段类型、字段顺序是否完全一致
  2. 若发现结构不一致，先通过V$LOCK、V$SESSION视图检查是否有其他会话正在使用该临时表，确认无锁后再删除旧临时表，用你现有逻辑重建新的空临时表
  3. 结构校验/重建完成后，再执行后续的分区数据插入、DQ校验操作
     核心校验逻辑参考：

  -- 计算两表字段数量差
  SELECT (SELECT COUNT(1) FROM ALL_TAB_COLUMNS WHERE OWNER = 'SCHEMA_NAME' AND TABLE_NAME = 'ORIGINAL_TABLE')
       - (SELECT COUNT(1) FROM ALL_TAB_COLUMNS WHERE OWNER = 'SCHEMA_NAME' AND TABLE_NAME = 'TEMP_TABLE')
  INTO V_COL_DIFF FROM DUAL;
  
  -- V_COL_DIFF不为0时，进一步对比字段名、类型，确认不一致后执行重建
  

• 定时任务预同步（次优先级）
  适配你的日分区数据场景：创建一个数据库定时JOB，在每日DQ任务启动前的低峰窗口，自动执行上述的结构对比、重建逻辑。不需要在每次存储过程运行时做检查，只要保证每日任务启动前临时表结构和当日原表结构一致即可，开销更低。
• 会话级私有临时表替代全局临时表（12c及以上版本适用）
  如果你使用的是Oracle 12c及以上版本，可以完全放弃固定的全局临时表：每次存储过程启动时，基于当前原表结构动态创建会话级私有临时表，插入数据完成校验后，会话结束临时表自动销毁，完全不会有锁冲突，也不需要长期维护临时表对象。

问题3：除了在插入SQL中手动枚举所有已知/预期字段外，是否有其他优化建议？ #

• 不要硬编码字段列表，也不要直接用无差别的SELECT *插入全字段：临时表重建完成后，可以从ALL_TAB_COLUMNS视图动态查询你DQ校验实际需要的字段，拼接成插入语句。你的原表有700余个字段，通常DQ校验只会用到其中几十到上百个字段，过滤掉不需要的大字段（比如CLOB、BLOB类型字段），能大幅降低插入时的IO和临时表空间占用，1000-3000万量级的数据下，执行效率能提升2-5倍。
• 插入分区数据时增加/*+ APPEND */直接路径插入提示，针对全局临时表的直接路径插入不会产生额外的回滚日志，插入速度比常规插入快60%以上，配合你ON COMMIT DELETE ROWS的属性，不会有临时段空间泄漏问题。
• 数据插入完成后再给临时表创建DQ校验需要的索引（比如空值校验、唯一性校验关联的字段），不要提前建索引，避免插入过程中维护索引带来的额外开销。
• 动态拼接插入语句时，可以对类型发生变更的字段增加显式类型转换，避免第三方厂商修改字段类型（比如把NUMBER改成VARCHAR2）时，隐式转换导致的报错或者数据精度丢失。

内容的提问来源于stack exchange，提问作者chora