如何查看是什么删除了我在Oracle中的行

0sgqnhkj  于 2023-08-03  发布在  Oracle
关注(0)|答案(1)|浏览(103)

每天晚上和晚上8点删除表中的行。有没有什么方法可以监视和查看是哪个过程或SQL执行了这些操作?该表具有ON DELETE触发器,它向我显示记录在昨晚被删除。我再次插入行,但发生了同样的事情。是否有任何表存储一些SQL历史国外所有用户?
我试图找到一个包含历史数据的表,但找不到。

mcvgt66p

mcvgt66p1#

审计或丰富的触发器日志记录是捕获每个可能的DML的唯一方法。但你也许不用它也能得到答案。
Oracle每秒记录一次每个会话的等待状态,并将该信息存储在内存中的ASH(* 活动会话历史记录 *)中,然后将这些样本中的10个中的1个刷新到一个表(在磁盘上),该表通常保留数周,具体取决于配置。只要您的删除活动花费了超过1秒并且发生在过去几个小时内,或者花费了超过10秒并且发生在过去几周内,您应该能够通过查询ASH数据来定位活动并查看是谁做的。以下是方法:
对于最近的活动:

SELECT sa.sql_id,
           sa.sql_text,
           ash.sample_time,
           ash.inst_id,
           ash.session_id,
           ash.session_serial#,
           u.username,
           ash.module,
           ash.program,
           ash.machine,
           ash.action,
           COUNT(DISTINCT ash.sample_time) secs,
           MIN(ash.sample_time) start_time,
           MAX(ash.sample_time) end_time
      FROM gv$active_session_history ash,
           gv$sqlarea sa,
           dba_users u
     WHERE ash.sql_id = sa.sql_id
       AND ash.inst_id = sa.inst_id
       AND sa.command_type IN (7,189) -- delete or merge (which can also do a delete)
       AND ash.sample_time BETWEEN ((TRUNC(SYSDATE)-1)+17/24) AND ((TRUNC(SYSDATE)-1)+19/24) -- between 7pm and 9pm last night
       AND ash.user_id = u.user_id
    GROUP BY sa.sql_id,
             sa.sql_text,
             ash.sample_time,
             ash.inst_id,
             ash.session_id,
             ash.session_serial#,
             u.username,
             ash.module,
             ash.program,
             ash.machine,
             ash.action

字符串
如果您得到了结果,请浏览它们,看看是否看到了将从有问题的表中删除行的语句。ASH只会在内存中保存信息很短的时间,所以如果它已经被刷新了,你可能不得不使用历史版本:

SELECT sa.sql_id,
           CAST(SUBSTR(sa.sql_text,1,2000) AS varchar2(2000)) sql_text,
           ash.sample_time,
           ash.instance_number,
           ash.session_id,
           ash.session_serial#,
           u.username,
           ash.module,
           ash.program,
           ash.machine,
           ash.action,
           COUNT(DISTINCT ash.sample_time)*10 secs,
           MIN(ash.sample_time) start_time,
           MAX(ash.sample_time) end_time
      FROM dba_hist_active_sess_history ash,
           dba_hist_sqltext sa,
           dba_users u
     WHERE ash.sql_id = sa.sql_id
       AND sa.command_type IN (7,189) -- delete or merge (which can also do a delete)
       AND ash.sample_time BETWEEN ((TRUNC(SYSDATE)-1)+17/24) AND ((TRUNC(SYSDATE)-1)+19/24) -- between 7pm and 9pm last night
       AND ash.user_id = u.user_id
    GROUP BY sa.sql_id,
             CAST(SUBSTR(sa.sql_text,1,2000) AS varchar2(2000)),
             ash.sample_time,
             ash.instance_number,
             ash.session_id,
             ash.session_serial#,
             u.username,
             ash.module,
             ash.program,
             ash.machine,
             ash.action

相关问题