Error TriggersPosted: August 4, 2007
Once every few weeks this customer encountered an ORA-1031 error – insufficient privileges. We saw the error in the alert log, and the customer complained about getting the error, but for some reason we never had enough information on what the customer was actually doing when the error occurred. We needed to know what he was doing to figure out how we can fix the issue.
We tried tracing, but the issue occurred so rarely that we were never able to actually capture it on time.
I thought we can solve the issue with error triggers. My suggestion was rejected by my team lead who prefers not to use triggers on production, but I still think it could be useful.
create table caught_errors ( dt date, username varchar2( 30), -- value from ora_login_user msg varchar2(512), stmt varchar2(512) );
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE declare sql_text ora_name_list_t; msg_ varchar2(2000) := null; stmt_ varchar2(2000) := null; BEGIN IF (IS_SERVERERROR (1031)) THEN for depth in 1 .. ora_server_error_depth loop msg_ := msg_ || ora_server_error_msg(depth); end loop;
for i in 1 .. ora_sql_txt(sql_text) loop stmt_ := stmt_ || sql_text(i); end loop;
insert into caught_errors (dt , username ,msg ,stmt ) values (sysdate, ora_login_user,msg_,stmt_); END IF; END;