1 、故障描述
某次某用户，误操作执行 `CREATE OR REPLACE PROCEDURE` 语句时，误将业务用户的关键存储过程覆盖，且操作时间点距发现时已超过数小时。用户急需找回原始存储过程逻辑，否则将影响后续业务流程。
2 、根因分析
（ 1 ）、创建原始存储过程
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 1; -- 原始自增步长：1
-- 循环终止条件：插入10 万条后退出
IF v_ind = 100000 THEN
EXIT; -- 替代return ，PL/SQL 循环中用EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码：' || SQLCODE || ' ，错误信息：' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
（ 2 ）、模拟误操作覆盖存储过程
修改两个核心常量：将 `v_ind` 自增步长由 1 改为 2 ，循环终止条件由 10 万条改为 30 万条：
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到 mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入 1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 2; -- 修改后自增步长： 2
-- 循环终止条件：插入 30 万条后退出
IF v_ind = 300000 THEN
EXIT; -- 替代 return ， PL/SQL 循环中用 EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码： ' || SQLCODE || ' ，错误信息： ' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
（ 3 ）、手工切换归档日志
确保误操作记录写入目标归档日志，便于后续挖掘：
ALTER SYSTEM ARCHIVE LOG CURRENT;
通过 `ARCHIVE LOG LIST` 确认当前归档号为 179 ，目标归档日志文件为 `/oradata/archivelog/1_179_1203889318.dbf` 。
（ 4 ）、创建两个匿名块
检查匿名块：快速统计日志中各对象的操作类型和次数，验证「删除旧源码 → 插入新源码」的猜想；
建表匿名块：将日志数据持久化到物理表，便于后续筛选、排序和细节分析，避免重复解析日志。
检查匿名块
set linesize 5000
SET SERVEROUTPUT ON SIZE UNLIMITED;
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('/oradata/archivelog/1_179_1203889318.dbf');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
FOR rec IN (
SELECT SEG_OWNER, SEG_NAME, OPERATION ,count(*) as op_count
FROM V$LOGMNR_CONTENTS
WHERE 1=1 group by SEG_OWNER, SEG_NAME, OPERATION order by SEG_OWNER, SEG_NAME, OPERATION,op_count
) LOOP
DBMS_OUTPUT.PUT_LINE('SEG_OWNER: ' || RPAD(rec.SEG_OWNER, 10) ||
' | SEG_NAME: ' || RPAD(rec.SEG_NAME, 40) ||
' | OPERATION: ' || RPAD(rec.OPERATION, 10) ||
' | op_count: ' || RPAD(rec.op_count, 10));
END LOOP;
DBMS_LOGMNR.END_LOGMNR;
END;
/
建表匿名块
set linesize 5000
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('/oradata/archivelog/1_179_1203889318.dbf');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
EXECUTE IMMEDIATE '
CREATE TABLE log202512 AS
SELECT * FROM V$LOGMNR_CONTENTS
WHERE 1=1
';
DBMS_LOGMNR.END_LOGMNR;
DBMS_OUTPUT.PUT_LINE('Table created with data from V$LOGMNR_CONTENTS');
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_LOGMNR.END_LOGMNR;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 忽略 LogMiner 已关闭的情况
END;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
/
3 、解决方案
原存储过程 DDL 语句提取：
执行「建表」匿名块后，日志数据已持久化到 `log202512` 表。接下来通过多轮查询，逐步定位原始源码：
（ 1 ）第一轮查询：直接查询业务用户对象
最初尝试直接筛选 `SEG_OWNER='MARY'` 的记录，期望获取存储过程的创建语句：
SELECT
SCN, TIMESTAMP, OPERATION, SQL_REDO, SQL_UNDO
FROM
sys.log202512
WHERE SEG_OWNER='MARY';
结果分析： `SQL_REDO` 字段确实包含两次 `CREATE OR REPLACE PROCEDURE` 语句，但 `SQL_UNDO` 均为空。真实环境中归档日志通常不会保留存储过程第一次创建的完整记录，仅靠业务用户对象查询无法获取旧版本。
（ 2 ）第二轮查询：挖掘系统表关键操作
既然业务用户对象查询无果，转而聚焦系统表 `SYS.SOURCE$` 和 `SYS.PROCEDURE$` ，筛选有有效 `SQL_UNDO` 的记录：
SELECT
SCN, TIMESTAMP, XIDUSN, XIDSQN, OPERATION,
SEG_OWNER, SEG_NAME, SQL_REDO, SQL_UNDO
FROM
sys.log202512
WHERE
SQL_UNDO IS NOT NULL
AND SQL_UNDO <> 'Unsupported'
AND SEG_OWNER = 'SYS'
AND SEG_NAME IN ('SOURCE$', 'PROCEDURE$');
（ 3 ）关键记录解析：还原原始源码
查询结果中，两条核心事务记录揭示了完整流程：
事务 1 ：存储过程第一次创建（ SCN=5179831 ， TIMESTAMP=2025-12-14 00:46:58 ）
首先向 `SYS.PROCEDURE$` 插入元数据：
INSERT INTO "SYS"."PROCEDURE$"("OBJ#","AUDIT$","STORAGESIZE","OPTIONS")
VALUES ('88608','--------------------------------------',NULL,'0');
随后向 `SYS.SOURCE$` 插入 28 行源码（按行号排序），例如：
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','1','PROCEDURE insert_test_data¶');
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','2','IS¶');
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','3',' v_ind INTEGER; -- 声明循环变量 ¶');
...
INSERT INTO "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") VALUES ('88608','14',' v_ind := v_ind + 1;¶');
这部分记录完整呈现了原始存储过程的源码结构， `OBJ#=88608` 是该存储过程的唯一标识。
事务 2 ：存储过程覆盖修改（ SCN=5179853 ， TIMESTAMP=2025-12-14 00:47:16 ）
首先更新 `SYS.PROCEDURE$` 的元数据：
UPDATE "SYS"."PROCEDURE$"
SET "AUDIT$" = '--------------------------------------', "OPTIONS" = '0'
WHERE "OBJ#" = '88608' AND ROWID = 'AAAADdAABAAAVH8AAi';
随后执行 28 条 `DELETE` 语句，删除 `SYS.SOURCE$` 中 `OBJ#=88608` 的旧源码（即原始存储过程），例如：
DELETE FROM "SYS"."SOURCE$"
WHERE "OBJ#" = '88608' AND "LINE" = '14' AND "SOURCE" = ' v_ind := v_ind + 1;¶' AND ROWID = 'AAAADgAABAAAWZjAAY';
关键亮点：这些 DELETE 语句的 `SQL_UNDO` 字段，正是恢复原始源码的核心 —— 每一条 DELETE 的 SQL_UNDO 都是一条 INSERT 语句，对应原始源码的一行。
最后执行 28 条 `INSERT` 语句，插入修改后的新源码（例如 `v_ind := v_ind + 2;` ）。
（ 4 ）最终提取：原始 DDL 语句还原
将事务 2 中 `SYS.SOURCE$` 表的 28 条 `DELETE` 操作对应的 `SQL_UNDO` 字段提取出来，按 `LINE` 行号排序，去除换行符 `¶` 并格式化，即可完整还原原始存储过程的逻辑：
CREATE OR REPLACE PROCEDURE mary.insert_test_data
IS
v_ind INTEGER; -- 声明循环变量
BEGIN
v_ind := 0; -- 初始化变量
LOOP
-- 插入数据到 mary.tt 表
INSERT INTO mary.tt(id, time)
VALUES(v_ind, SYSDATE);
-- 每插入 1 条提交（原逻辑保留，若想批量提交可调整）
COMMIT;
v_ind := v_ind + 1;
-- 循环终止条件：插入 10 万条后退出
IF v_ind = 100000 THEN
EXIT; -- 替代 return ， PL/SQL 循环中用 EXIT 更规范
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常时回滚并打印错误信息（便于测试排错）
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(' 错误码： ' || SQLCODE || ' ，错误信息： ' || SQLERRM);
RAISE; -- 可选：重新抛出异常，便于外层捕获
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30310891/viewspace-3110295/ ，如需转载，请注明出处，否则将追究法律责任。
