oracle如何获取特定时间范围内失败的事务和 SQL 的数量

创建触发器进行记录

create table trace( id number, seq number, msg varchar2(4000),dt date );
 create sequence s;

 create or replace trigger failed_sqls
 after servererror on database
  declare
  l_sql_text ora_name_list_t;
  l_n number;
  begin
  -- if ( is_servererror(942) ) then --------- 可以过滤特定错误，
  insert into trace values ( s.nextval, 1, 'ora_sysevent = ' || ora_sysevent,sysdate );
  insert into trace values ( s.currval, 2, 'ora_login_user = ' || ora_login_user,sysdate );
  insert into trace values ( s.currval, 3, 'ora_server_error = ' || ora_server_error(1),sysdate );

  l_n := ora_sql_txt( l_sql_text );
  for i in 1 .. l_n
  loop
  insert into trace values ( s.currval, 3+i, 'l_sql_text(' || i || ') = ' ||
l_sql_text(i),sysdate );
  end loop;
  -- end if; --------- 可以过滤特定错误
  end;
  /

-- 查询
SELECT * FROM trace;

--用完清理
DROP TRIGGER failed_sqls;
DROP table trace;
drop sequence s;