Very recently we had a production scenario where the client
application produced ORA 22163 in the application logs, but doesn’t report the
sql causing this issue. Application developers were asking can this sql causing
ORA 22163 be identified in the database?
We took an approach of schema level server error triggers (again
completely transparent to applications). Just created the below error_table
along with the servererror trigger, with the help of DBA on Application schema.
Then ran the application workload/scenario that is causing this error. Once the error ORA 22163 occurs in the
database, automatically the sqltext along with the error message will be stored
into this error_Table.
rajesh@ORA10G>
create table errors_table
2 (dt
date default sysdate,
3
logon_user varchar2(30) default user,
4
err_no number,
5
err_msg varchar2(50),
6
sql_txt varchar2(500) );
Table created.
Elapsed:
00:00:00.06
rajesh@ORA10G>
create or replace trigger catch_errors
2
after servererror on rajesh.schema
3
declare
4
pragma autonomous_transaction ;
5
sql_txt ora_name_list_t;
6
l_stmt long;
7
begin
8
for i in 1..ora_sql_txt(sql_txt)
9
loop
10
l_stmt := l_stmt || sql_txt(i);
11
end loop;
12
13
for i in 1..ora_server_error_depth
14
loop
15
insert into errors_table
16
(logon_user, err_no, err_msg,sql_txt)
17
values(ora_login_user, ora_server_error(i),
18 ora_server_error_msg(i),
l_stmt );
19
end loop;
20
commit;
21 end;
22 /
Trigger
created.
Elapsed:
00:00:00.09
rajesh@ORA10G>
select * from errors_table;
no rows
selected
Elapsed:
00:00:00.01
rajesh@ORA10G>
select * from emp55;
select
* from emp55
*
ERROR
at line 1:
ORA-00942:
table or view does not exist
Elapsed:
00:00:00.07
rajesh@ORA10G>
rajesh@ORA10G>
column dt noprint
rajesh@ORA10G>
select * from errors_table;
LOGON_USER ERR_NO ERR_MSG SQL_TXT
----------
---------- ---------------------------------------- ----------------------
RAJESH 942 ORA-00942:
table or view does not exist select * from emp55
1 row selected.
Elapsed:
00:00:00.01
rajesh@ORA10G>
No comments:
Post a Comment