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