Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

servererror trigger stopped working after copy/clone db

User_8P4FCOct 19 2012 — edited Oct 19 2012
hello
we have a dev server, where the following trigger was enabled under the user, with dba/admin rights
create or replace
trigger 
   log_errors_trig
after servererror on database

declare
 --  VAR_USER      varchar2(30);
   var_osuser    varchar2(30);
   var_machine   varchar2(64);
   var_process   varchar2(12);
   var_program   varchar2(64);
	 sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
	 
begin

if ORA_LOGIN_USER != 'DBSNMP' and  ORA_LOGIN_USER != 'DBSNMP' and  ORA_LOGIN_USER != 'SYSMAN' and ORA_LOGIN_USER != 'OWBREPOS_OWNER' and ORA_LOGIN_USER != 'OWF_MGR'
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;

   select 
   --   username,
      osuser,
      machine,
      process,
      program
   into 
    --  var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program
   from 
      v$session
   where 
      audsid=userenv('sessionid');
			
   insert into 
      SERVERERROR_LOG
   values(
      DBMS_STANDARD.SERVER_ERROR(1),
			DBMS_STANDARD.SERVER_ERROR_MSG(1),
      systimestamp,
      ora_login_user,
      var_osuser,
      var_machine,
      var_process,
      var_program,
			stmt_);
	END IF;
end;
to my knowledge there were some admin tasks performed i.e copying this server to new enviroment with new ip, to create a new copy, and possibly a new one full installation, maybe with cloning;
anway: since these admin operations, this trigger on the original server is no longer working;
the errors are also thrown in a different way, and the trigger is not capturing them anymore

i.e. on my local machine if i do "select * from x" , x being a non existing table i recevie the error:
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 42 Column: 14
the error is kept in the error table with the following entry
INFO	ERROR_MSG	TIME_ERROR	USER_NAME	OSUSER	MACHINE	PROCESS	PROG	SQL_TEXT
942	ORA-00942: table or view does not exist	16:20,7	ADMIN	XXX    XXX	4320	SQL Developer	select * X
in the dev server after issuing the same error statemtne the error message i receive now is:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at line 42
ORA-00942: table or view does not exist
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 42 Column: 14
the error message does not get caputured in the error table neither;

i would appreciate any tips on where to look for the source of this change and this eror in processing;
thanks very much
rgds
This post has been answered by John Spencer on Oct 19 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2012
Added on Oct 19 2012
1 comment
572 views