存储过程中执行DDL语句后异常回滚未生效，寻求解决方案

为啥回滚没用？ #

Oracle里所有DDL语句（比如ALTER TABLE DROP PARTITION）执行时都会触发隐式提交：

1. 执行DDL之前，Oracle会自动提交当前未完成的事务；
2. DDL语句本身执行完成后也会自动提交。
   所以你的存储过程里，execute IMMEDIATE 'ALTER TABLE a DROP PARTITION ...'一执行，删分区的操作就已经被自动提交了，后面触发异常再跑ROLLBACK根本救不回来。

怎么解决？ #

DDL本身没法回滚，要实现异常时“撤销”DDL的效果，得提前做准备，要么先记录要删的分区信息，异常时重建，要么换个方式处理。下面给两种可行的方案：

方案1：先收集待删分区，异常时重建 #

1. 先把符合条件的分区名和分区边界值（high_value）存起来；
2. 挨个删分区，要是出异常了，就根据之前存的信息把已经删掉的分区重新建回来（如果分区里有数据，得提前备份好，异常时再导回去）。

修改后的存储过程示例：

CREATE OR REPLACE PROCEDURE CLEAN_YQCALLSDR (
    saveMonths IN INTEGER, ret OUT VARCHAR2 ) AS
    -- 定义集合类型存分区名和边界值
    TYPE partition_name_list IS TABLE OF VARCHAR2(30);
    TYPE partition_highval_list IS TABLE OF VARCHAR2(100);
    v_drop_part_names partition_name_list := partition_name_list();
    v_drop_high_vals partition_highval_list := partition_highval_list();
    v_current_idx INTEGER;
BEGIN
    ret := 0;
    -- 第一步：先把要删的分区都找出来存好
    SELECT partition_name, high_value
    BULK COLLECT INTO v_drop_part_names, v_drop_high_vals
    FROM user_tab_partitions 
    WHERE table_name = 'A' -- Oracle表名默认大写，别写小写
      AND to_date(substr(high_value, 11, 10), 'yyyy-mm-dd') 
          BETWEEN add_months(SYSDATE, -24) AND add_months(SYSDATE, -saveMonths);
    
    -- 第二步：遍历删除分区
    v_current_idx := 0;
    FOR i IN 1..v_drop_part_names.COUNT LOOP
        v_current_idx := i;
        execute IMMEDIATE 'ALTER TABLE a DROP PARTITION ' || v_drop_part_names(i);
        -- 人为制造异常的测试代码
        -- ret := ret/0;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('错误代码 : ' || sqlcode);
        DBMS_OUTPUT.put_line('错误信息 : ' || sqlerrm);
        ret := 1;
        -- 异常时，把已经删掉的分区重建回来（仅限范围分区，其他分区类型要调整语句）
        IF v_current_idx > 0 THEN
            FOR j IN 1..v_current_idx LOOP
                execute IMMEDIATE 'ALTER TABLE a ADD PARTITION ' || v_drop_part_names(j) || 
                                  ' VALUES LESS THAN (' || v_drop_high_vals(j) || ')';
                -- 这里要加数据恢复的逻辑，比如从备份导入该分区的数据
            END LOOP;
        END IF;
        -- 回滚其他可能的DML操作（如果有的话）
        ROLLBACK;
END;

方案2：用自治事务隔离DDL（仅隔离，不能回滚） #

要是你不需要回滚DDL，只是不想让DDL的提交影响主事务里的其他操作，可以把DDL封装到自治事务里。但注意，这还是没法回滚DDL，只是把DDL的事务和主事务分开：

-- 封装删分区的自治事务过程
CREATE OR REPLACE PROCEDURE drop_single_partition(p_part_name VARCHAR2) AS
    PRAGMA AUTONOMOUS_TRANSACTION; -- 标记为自治事务
BEGIN
    execute IMMEDIATE 'ALTER TABLE a DROP PARTITION ' || p_part_name;
    COMMIT; -- 自治事务必须显式提交或回滚
END;

然后在主存储过程里调用这个过程就行，这样删分区的提交不会影响主事务里的其他操作，但删了还是删了，没法回滚。

注意点 #

• Oracle里DDL天生不可回滚，所有要“撤销”DDL的需求都得靠提前备份+异常恢复实现；
• 记得Oracle表名默认是大写的，查询user_tab_partitions时别写小写的表名，不然查不到；
• 重建分区时，要保证分区的定义和原来完全一样，要是分区里有数据，一定要提前备份好，比如用EXPDP导出分区，异常时再IMPDP导回去。

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