is there any way to log all failed sql statements in oracle 10g

Question

is there any way to log all failed sql statements in oracle 10g to a table or file?

By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object.

Answer 1

You can do this with a system trigger.

I directly copied this code from 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;
    /

Answer 2

Rather than hit the system views, as in Demge's answer, there is an ora_sql_txt function that gives the relevant statement.

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) 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;

In my own environment, I actually have 'TRACK' as a separate procedure that uses an autonomous transaction, rather than a block as above.

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

You may want to use Auditing like:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

By ACCESS is for each statement (which seems like what you want). By SESSION would record one record per session (high volume environment).

Oracle's built in auditing has less overhead then a trigger. A trigger, like above, allows you to log the exact information you want. Auditing will also only catch hits on existing objects. If someone selects on a non-existent table (misspelled or whatnot) auditing will not catch it. The triggers above will.

A lot more info in the security guide: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984