Oracle 11g：能否跨会话备份未提交数据，回滚原会话后保留备份？

你期望的直接在另一个会话读取当前未提交会话的数据并创建备份表是不可行的。原因是Oracle的多版本并发控制（MVCC）机制：未提交的事务修改仅对当前会话可见，其他会话无论是读已提交还是可串行化隔离级别，都只能读到已提交的数据或事务启动时的快照，无法获取当前会话未提交的变更。

不过你提到的自治事务（PRAGMA AUTONOMOUS_TRANSACTION）确实是解决这个需求的最优方案，而且可以简化操作，不需要手动切换会话。

核心原理 #

自治事务是独立于主事务的子事务，它的提交/回滚不会影响主事务的状态，主事务回滚也不会撤销自治事务已经完成的操作。我们可以利用这一点，在当前会话中通过自治事务完成数据备份，之后回滚主事务即可保留备份数据。

简化实现方式 #

方式1：匿名块快速执行

不需要创建存储过程，直接用匿名块完成备份：

-- 当前会话执行数据修改
INSERT INTO TableA (A,B,C) VALUES (1,2,3);

-- 用自治事务创建备份表
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- 如果目标表已存在，先删除（可选，根据需求调整）
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE TempA PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN -- 忽略表不存在的错误
                RAISE;
            END IF;
    END;
    
    -- 创建备份表并提交自治事务
    EXECUTE IMMEDIATE 'CREATE TABLE TempA AS SELECT * FROM TableA';
    COMMIT;
END;
/

-- 回滚当前会话的所有修改
ROLLBACK;

-- 验证结果
SELECT * FROM TableA; -- 无数据
SELECT * FROM TempA; -- 会返回1,2,3

方式2：创建可复用的存储过程

如果需要多次执行备份，可以创建通用存储过程：

-- 创建备份存储过程
CREATE OR REPLACE PROCEDURE backup_uncommitted_data(p_source_table VARCHAR2, p_target_table VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- 清理已存在的目标表
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE ' || p_target_table || ' PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN
                RAISE;
            END IF;
    END;
    
    -- 执行备份
    EXECUTE IMMEDIATE 'CREATE TABLE ' || p_target_table || ' AS SELECT * FROM ' || p_source_table;
    COMMIT;
END;
/

使用时只需调用存储过程：

INSERT INTO TableA (A,B,C) VALUES (1,2,3);

-- 调用备份过程
EXEC backup_uncommitted_data('TABLEA', 'TEMPA');

ROLLBACK;

SELECT * FROM TableA; -- 无数据
SELECT * FROM TempA; -- 数据保留

注意事项 #

• 自治事务必须显式执行COMMIT或ROLLBACK，否则会抛出异常。
• 匿名块或存储过程中的DDL操作（如CREATE TABLE）会隐式提交自治事务，但显式添加COMMIT更符合规范。
• 确保执行备份的用户拥有足够的权限（创建表、删除表、查询源表）。

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