Oracle 11g下能否在触发器中刷新Materialised View及方案咨询

Oracle 中物化视图刷新会触发隐式提交，而行级触发器运行在插入事务的上下文中，事务未提交时不允许执行隐式提交操作，因此必须通过异步解耦的方式实现需求，以下是三种适配不同场景的实现方案：

方案1：触发器内提交异步作业（最适配现有逻辑） #

通过Oracle内置的调度组件将刷新操作改为异步执行，作业仅在当前插入事务提交后才会触发，完美避开隐式提交冲突。
Oracle 11g 可直接使用DBMS_JOB实现，修改后的触发器逻辑如下：

DROP TRIGGER SYSADM.COMPLETE_NOTIF_SMS;

CREATE OR REPLACE TRIGGER SYSADM.COMPLETE_NOTIF_SMS
AFTER INSERT
ON SYSADM.HISTORY_TABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
   V_STATUS   NUMBER;
   V_NOTIFICATION_TEXT VARCHAR2(100);
   V_CHECK_CATEGORY VARCHAR2(100);
   V_JOB_NO NUMBER;
BEGIN
      insert into some_table values (v_check_category, v_notification_text,sysdate);  
      -- 提交一次性异步作业执行物化视图刷新
      DBMS_JOB.SUBMIT(
        JOB => V_JOB_NO,
        WHAT => 'DBMS_SNAPSHOT.REFRESH(''mview_to_refresh'');',
        NEXT_DATE => SYSDATE,
        INTERVAL => NULL
      );
EXCEPTION
   WHEN OTHERS
   THEN
      -- 原有发送邮件逻辑保留
      RAISE;
END COMPLETE_NOTIF_SMS;
/

注意事项： #

• 需要提前给SYSADM用户授予DBMS_JOB的执行权限，以及物化视图的刷新权限
• 作业仅在当前插入事务提交后才会被调度执行，若插入事务回滚，作业也会同步回滚，不会出现无效刷新
• 若需要刷新失败告警，可将刷新逻辑封装为独立存储过程，在存储过程内增加异常捕获和通知逻辑，再将存储过程放到DBMS_JOB中执行

方案2：修改插入批处理逻辑（优先推荐，维护成本更低） #

因为HISTORY_TABLE每月仅插入一次，插入动作通常是固定的批处理任务，可直接在批处理的插入逻辑、事务提交完成后，新增调用DBMS_SNAPSHOT.REFRESH的代码。
该方案无需新增触发器和异步作业，链路更短，排查问题更简单。

方案3：高级队列（AQ）异步通知（适合复杂扩展场景） #

如果后续有多个下游动作需要在插入后触发，可通过Oracle高级队列实现事件通知，消费者进程收到插入事件后执行刷新操作，该方案耦合度更低但实现复杂度更高，当前场景下不推荐。

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