Oracle中如何排查删除表行数据的操作来源

一、定位删除操作的存储过程或SQL语句 #

1. 增强DELETE触发器 #

在现有ON DELETE触发器中添加日志记录逻辑，直接捕获删除操作的会话信息、SQL文本及调用栈：

CREATE OR REPLACE TRIGGER your_table_delete_trigger
AFTER DELETE ON your_table
FOR EACH ROW
DECLARE
    v_sql_text CLOB;
    v_call_stack VARCHAR2(4000);
    v_username VARCHAR2(100);
    v_session_id NUMBER;
BEGIN
    -- 获取当前会话的用户名和ID
    SELECT username, sid INTO v_username, v_session_id 
    FROM v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
    
    -- 获取当前执行的SQL文本
    SELECT sql_fulltext INTO v_sql_text 
    FROM v$sqlarea 
    WHERE address = (SELECT sql_address FROM v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID'));
    
    -- 获取调用栈（追踪存储过程调用链）
    v_call_stack := DBMS_UTILITY.format_call_stack;
    
    -- 插入自定义日志表（需提前创建delete_operation_log表）
    INSERT INTO delete_operation_log (
        delete_time, username, session_id, sql_text, call_stack, deleted_row_id
    ) VALUES (
        SYSTIMESTAMP, v_username, v_session_id, v_sql_text, v_call_stack, :OLD.ROWID
    );
EXCEPTION
    WHEN OTHERS THEN
        -- 避免触发器异常阻断删除操作，可忽略或记录到告警表
        NULL;
END;
/

若为批量删除，可改为STATEMENT级触发器减少日志写入次数。

2. 启用Oracle审计功能 #

针对目标表开启DELETE操作审计，直接捕获操作发起者、时间及执行语句：

-- 启用目标表的DELETE审计（需DBA权限）
AUDIT DELETE ON your_table BY ACCESS;

-- 查询审计记录
SELECT username, timestamp, sql_text 
FROM dba_audit_trail 
WHERE obj_name = 'YOUR_TABLE' AND action_name = 'DELETE';

审计记录默认存储在SYS.AUD$表，可通过DBA_AUDIT_TRAIL视图查询，注意配置审计保留策略避免磁盘占用过高。

3. 使用SQL Trace或DBMS_MONITOR #

针对目标会话或用户开启SQL Trace，捕获完整的执行SQL及绑定变量：

-- 针对特定会话开启跟踪（替换为实际session_id和serial_num）
EXEC DBMS_MONITOR.session_trace_enable(session_id => 123, serial_num => 456, waits => TRUE, binds => TRUE);

-- 或者针对特定用户开启跟踪
EXEC DBMS_MONITOR.client_id_trace_enable(client_id => 'APP_USER', waits => TRUE, binds => TRUE);

跟踪文件生成在Oracle的USER_DUMP_DEST目录下，可使用TKPROF工具分析，提取DELETE相关SQL及调用栈信息。

4. 查询AWR/ASH视图 #

若删除操作发生在近期（AWR保留周期内，默认7天），可通过归档视图查询历史SQL：

-- 查询目标表相关的DELETE语句
SELECT sql_id, sql_text, parsing_schema_name, elapsed_time
FROM dba_hist_sqltext t
JOIN dba_hist_sqlstat s ON t.sql_id = s.sql_id
WHERE t.sql_text LIKE '%DELETE%your_table%'
ORDER BY s.snap_id DESC;

V$ACTIVE_SESSION_HISTORY视图可查看近1小时内的活跃会话SQL，适合实时监控场景。

二、Oracle中存储用户SQL执行历史的表/视图 #

Oracle没有永久存储所有用户SQL执行历史的表，但可通过以下视图查询近期或归档的SQL：

• V$SQL/V$SQLAREA：存储当前实例共享池中的缓存SQL，SQL被清除或实例重启后数据丢失。
• DBA_HIST_SQLTEXT/DBA_HIST_SQLSTAT：AWR归档的SQL历史，保留周期由AWR配置决定（默认7天），仅归档执行次数多、资源消耗高的SQL，并非全部。
• V$SQL_PLAN：可查看SQL的执行计划，辅助判断是直接SQL还是存储过程调用。

若需长期保存所有SQL执行记录，需通过自定义审计脚本、第三方监控工具，或配置Oracle统一审计（Unified Auditing）实现。

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