Is there a way to register all failed SQL commands in oracle 10g

Question

Is there a way to write all failed SQL commands in oracle 10g to a table or file?

Failure, I mean a poorly formulated SQL statement or SQL queries that do not have permission for a table or object.

Answer 1

You can use Audit, for example:

TABLE AUDIT SELECTION, INSERT TABLE, DELETE TABLE, ACCESSING THE ACCESS PROCEDURE WHEN NOT SUCCESSFUL;

ACCESS - for each statement (which looks like what you want). SESSION will record one record per session (high volume environment).

Oracle's built-in audit has less overhead than a trigger. The trigger, which contains other answers, allows you to log the information you need. An audit will also capture only hits on existing facilities. If someone selects on a nonexistent table (with an error or something else), the audit will not catch it. Triggers will be higher.

Additional information in the security manual: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984

Answer 2

Instead of hitting system views, as in Demge's answer, there is an ora_sql_txt function that gives the corresponding operator.

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; 

In my own environment, I actually have TRACK, as a separate procedure, using an autonomous transaction, and not a block, as mentioned 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 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; /