有没有办法记录oracle 10g中所有失败的sql语句？

Question

有没有办法将oracle 10g中所有失败的sql语句记录到一个表或文件中？

我所说的失败是指格式错误的sql语句或对表或对象没有权限的sql语句。

Answer 1

您可能希望像这样使用审计：

审计SELECT TABLE、INSERT TABLE、DELETE TABLE、EXECUTE PROCEDURE BY ACCESS；

按访问是针对每个语句的(这似乎是您想要的)。按会话将为每个会话记录一条记录(大容量环境)。

Oracle的内置审计比触发器具有更少的开销。其他答案包含的触发器允许您记录所需的确切信息。审计还将仅捕获现有对象上的命中。如果有人选择了一个不存在的表(拼写错误或诸如此类的东西)，审计将不会捕获它。上面的触发器将会。

有关更多信息，请参阅安全指南：http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984

Answer 2

有一个ora_sql_txt函数给出了相关的语句，而不是像在Demge的答案中那样点击系统视图。

create or replace TRIGGER log_err after servererror on schema
DECLARE
  v_stack VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000);
  v_back VARCHAR2(2000);-- := substr(dbms_utility.format_error_backtrace,1,2000);
  v_num NUMBER;
  v_sql_text ora_name_list_t;
  procedure track(p_text in varchar2) is
  begin
     insert into .... values (p_text);
  end;
begin
  v_stack := translate(v_stack,'''','"');
  track(v_stack);
  v_back := translate(v_back,'''','"');
  if v_back is not null then track(v_back); end if;
  v_num  := ora_sql_txt(v_sql_text);
  BEGIN
    FOR i IN 1..v_num LOOP
      track(to_char(i,'0000')||':'||v_sql_text(i));
    END LOOP;
  EXCEPTION
    WHEN VALUE_ERROR THEN NULL;
  END;
end;

在我自己的环境中，我实际上将“TRACK”作为一个独立的过程来使用自治事务，而不是像上面那样使用块。

create or replace procedure track (p_text IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  cursor c_user is
    select   sys_context('USERENV','CLIENT_INFO')       client_info,
             sys_context('USERENV','CURRENT_SCHEMA')    curr_schema,
             sys_context('USERENV','CURRENT_USER')      curr_user,
             sys_context('USERENV','DB_NAME')           db_name,
             sys_context('USERENV','HOST')              host,
             sys_context('USERENV','IP_ADDRESS')        ip,
             sys_context('USERENV','OS_USER')           osuser,
             sys_context('USERENV','SESSIONID')         sessid,
             sys_context('USERENV','SESSION_USER')      sess_user,
             sys_context('USERENV','TERMINAL')          terminal
  from dual;
  user_rec c_user%rowtype;
  v_mod  VARCHAR2(48);
  v_act  VARCHAR2(32);
  v_cli_info varchar2(64);
begin
  open c_user;
  fetch c_user into user_rec;
  close c_user;
  DBMS_APPLICATION_INFO.READ_MODULE (v_mod, v_act);
  --DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_cli_info);
  insert into track_detail
    (id, track_time, detail, client_info, curr_schema, curr_user, db_name, 
     host, ip, osuser, sessid, sess_user, terminal, module, action)
  values (track_seq.nextval, systimestamp, p_text,
          user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, 
          user_rec.db_name,     user_rec.host,        user_rec.ip, 
          user_rec.osuser,      user_rec.sessid,      user_rec.sess_user, 
          user_rec.terminal,    v_mod,                v_act);
  commit;
end;

Answer 3

您可以使用系统触发器来执行此操作。

我直接从http://www.psoug.org/reference/system_trigger.html复制了这段代码。

CREATE TABLE servererror_log (
error_datetime  TIMESTAMP,
error_user      VARCHAR2(30),
db_name         VARCHAR2(9),
error_stack     VARCHAR2(2000),
captured_sql    VARCHAR2(1000));





 CREATE OR REPLACE TRIGGER log_server_errors
    AFTER SERVERERROR
    ON DATABASE
    DECLARE
     captured_sql VARCHAR2(1000); 
    BEGIN
      SELECT q.sql_text
      INTO captured_sql
      FROM gv$sql q, gv$sql_cursor c, gv$session s
      WHERE s.audsid = audsid
      AND s.prev_sql_addr = q.address
      AND q.address = c.parent_handle;

      INSERT INTO servererror_log
      (error_datetime, error_user, db_name,
       error_stack, captured_sql)
      VALUES
      (systimestamp, sys.login_user, sys.database_name,
      dbms_utility.format_error_stack, captured_sql);
    END log_server_errors;
    /