Oracle数据库-反查客户端谁把存储过程编译失效了

在Oracle中，若某个存储过程被意外编译导致失效（例如因依赖对象缺失或编译错误），可通过以下步骤追踪是哪个客户端执行了该操作：

---

​1. 立即检查当前活动会话（实时排查）​

如果问题刚发生且操作者会话尚未断开，可通过以下查询实时定位：

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,          -- 操作系统用户
    s.machine,         -- 客户端机器名
    s.program,         -- 客户端工具（如PL/SQL Developer）
    s.client_identifier,
    sq.sql_text        -- 正在执行的SQL
FROM 
    v$session s
JOIN 
    v$sql sq ON s.sql_id = sq.sql_id
WHERE 
    sq.sql_text LIKE '%ALTER PROCEDURE YOUR_PROCEDURE%COMPILE%'
    OR sq.sql_text LIKE '%CREATE OR REPLACE PROCEDURE YOUR_PROCEDURE%';

字段说明：

• machine：显示客户端计算机名（如PC-001）。
• program：显示客户端工具名称（如plsqldev.exe）。
• sql_text：显示具体执行的编译语句。

权限要求：需有SELECT权限访问v$session和v$sql（通常需要DBA权限）。

---

​2. 查询最后一次编译时间（定位时间点）​

通过数据字典确认存储过程最后一次编译时间，缩小排查范围：

SELECT 
    owner,
    object_name,
    last_ddl_time,
    status
FROM 
    dba_objects
WHERE 
    object_name = 'YOUR_PROCEDURE'
    AND object_type = 'PROCEDURE';

输出示例：

OWNER    OBJECT_NAME       LAST_DDL_TIME       STATUS
------   ------------      -----------------   ------
HR       EMP_PROC          2023-10-25 14:30   INVALID

下一步：结合LAST_DDL_TIME时间点，进一步排查该时间附近的客户端操作。

---

​3. 检查审计日志（需提前启用审计）​

若已启用数据库审计，可通过以下查询追溯操作记录：

​**(1) 标准审计日志**

SELECT 
    os_username,       -- 操作系统用户
    username,          -- 数据库用户
    obj_name,          -- 对象名
    action_name,       -- 操作类型（如ALTER）
    timestamp,         -- 操作时间
    sql_text           -- 完整SQL语句
FROM 
    dba_audit_object
WHERE 
    obj_name = 'YOUR_PROCEDURE'
    AND action_name IN ('ALTER', 'CREATE')
    AND timestamp >= TO_DATE('2023-10-25 14:25', 'YYYY-MM-DD HH24:MI')
ORDER BY 
    timestamp DESC;

--查询审计日志
SELECT * FROM DBA_AUDIT_TRAIL 
WHERE TIMESTAMP >= 
TO_DATE('2023-10-25 14:25', 'YYYY-MM-DD HH24:MI') AND TIMESTAMP <= 
TO_DATE('2023-10-25 15:25', 'YYYY-MM-DD HH24:MI');

​**(2) 细粒度审计（FGA）日志**

SELECT 
    db_user,           -- 数据库用户
    os_user,           -- 操作系统用户
    client_program,    -- 客户端程序
    sql_text,          -- 执行的SQL
    timestamp
FROM 
    dba_fga_audit_trail
WHERE 
    object_name = 'YOUR_PROCEDURE'
    AND sql_text LIKE '%ALTER PROCEDURE%';

权限要求：需有SELECT权限访问DBA_AUDIT_OBJECT或DBA_FGA_AUDIT_TRAIL。

---

​4. 检查DDL触发器日志（需提前配置）​

若已创建DDL触发器记录对象修改日志，直接查询日志表：

SELECT 
    username,          -- 数据库用户
    os_user,           -- 操作系统用户
    machine,           -- 客户端机器名
    program,           -- 客户端工具
    action,            -- 操作类型（ALTER/CREATE）
    change_date        -- 操作时间
FROM 
    proc_audit_log     -- 替换为你的日志表名
WHERE 
    proc_name = 'YOUR_PROCEDURE'
ORDER BY 
    change_date DESC;

---

​5. 检查数据库日志文件

若未配置审计或触发器，可尝试从数据库日志中查找线索：

​**(1) Alert Log**

检查alert_<SID>.log中是否有编译操作的记录：

bash

 

# 定位alert.log路径
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

# 使用grep搜索日志（示例）
grep 'ALTER PROCEDURE HR.EMP_PROC' /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

​**(2) Trace Files**

若编译操作导致错误（如PLS-XXXXX），检查对应的跟踪文件：

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

---

​6. 后续建议：配置审计或触发器

为避免未来无法追溯，推荐永久启用以下监控：

​**(1) 启用标准审计**

AUDIT ALTER ANY PROCEDURE BY ACCESS;

​**(2) 创建DDL触发器**

CREATE OR REPLACE TRIGGER track_proc_compile
AFTER ALTER ON DATABASE
DECLARE
    v_obj_type VARCHAR2(30);
    v_obj_name VARCHAR2(30);
BEGIN
    IF (ORA_DICT_OBJ_TYPE = 'PROCEDURE') THEN
        v_obj_type := ORA_DICT_OBJ_TYPE;
        v_obj_name := ORA_DICT_OBJ_NAME;
        INSERT INTO proc_audit_log 
        VALUES (
            ORA_LOGIN_USER,
            SYS_CONTEXT('USERENV', 'OS_USER'),
            SYS_CONTEXT('USERENV', 'HOST'),
            SYS_CONTEXT('USERENV', 'MODULE'),
            'ALTER',
            SYSDATE
        );
    END IF;
END;
/

---

​总结

• ​实时排查：优先检查v$session和v$sql，适用于操作尚未结束的情况。
• ​审计/触发器：若已提前配置，直接查询日志表。
• ​数据库日志：作为兜底方案，需手动搜索日志文件。
• ​预防措施：务必启用审计或DDL触发器，确保未来可追溯性。