This post is inspired by the articles Part 1. Logging events in Oracle PL / SQL and Part 2. Identifying events happening in Oracle PL / SQL . First of all, as a performance tuning and troubleshooting specialist, I would like to comment on some of the nuances.
1. Levels of logging verbosity
The system shown lacks flexibility in configuring logging: both the level of detail and the place where to output them. It was possible to borrow functionality from well-known logging systems a la java.util.logging (SLF4j, log4j and its variations for other languages / systems, etc.), flexible configuration for which code from which level of messages and where to save them. For example, in the same log4plsql, you can customize the output in both alert.log and trace file (using `dbms_system.ksdwrt ()`)
2. Custom errors and messages
From the innermost Oracle error system it was possible to borrow the use of UTL_LMS.FORMAT_MESSAGE . By the way, the errors (and events) themselves can be viewed using sys.standard.sqlerrm (N):
SQL> select sys.standard.sqlerrm(-1476) errmsg from dual;
ERRMSG
-------------------------------------
ORA-01476: divisor is equal to zero
Examples: err_by_code.sq l, trace_events.sql
, , , , , - , exception handler . .
3.
, , - , . `after servererror on database/schema
`. .
, , , , , Oracle.
, Nenad Noveljic c "TNS-12599: TNS:cryptographic checksum mismatch
" callstack:
, "ERRORSTACK", "ACTIONS", "CALLSTACK":
12599 - (event), callstack - call , level 2 - , lifetime 1 - .
Tanel Poder :
, , "trace()" shortstack():
callstack:
alter system set events '12599 trace("stack is: %\n", shortstack())';
:
alter system set events 'kg_event[12599]{occurence: start_after 1, end_after 1} trace("stack is: %\n", shortstack())';
, : 1 .
"ORA-01476: divisor is equal to zero":
alter system set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %\n", shortstack())';
kg_event - Kernel Generic event, 1476 - ORA-1476. :
SQL> alter session set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %\n", shortstack())';
Session altered.
SQL> select 1/0 x from dual;
select 1/0 x from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL> select 1/0 x from dual;
select 1/0 x from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SQL> select 1/0 x from dual;
select 1/0 x from dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
:
# cat ORA19_ora_12981.trc
Trace file /opt/oracle/diag/rdbms/ora19/ORA19/trace/ORA19_ora_12981.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
Build label: RDBMS_19.9.0.0.0DBRU_LINUX.X64_200930
ORACLE_HOME: /opt/oracle/product/19c/dbhome_1
System name: Linux
Node name: b7c493c7f9b0
Release: 3.10.0-1062.12.1.el7.x86_64
Version: #1 SMP Tue Feb 4 23:02:59 UTC 2020
Machine: x86_64
Instance name: ORA19
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 12981, image: oracle@b7c493c7f9b0
*** 2021-05-08T14:12:27.000816+00:00 (PDB1(3))
*** SESSION ID:(251.9249) 2021-05-08T14:12:27.000846+00:00
*** CLIENT ID:() 2021-05-08T14:12:27.000851+00:00
*** SERVICE NAME:(pdb1) 2021-05-08T14:12:27.000855+00:00
*** MODULE NAME:(sqlplus.exe) 2021-05-08T14:12:27.000859+00:00
*** ACTION NAME:() 2021-05-08T14:12:27.000862+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-05-08T14:12:27.000865+00:00
*** CONTAINER ID:(3) 2021-05-08T14:12:27.000868+00:00
stack is: dbgePostErrorKGE<-dbkePostKGE_kgsf<-kgeade<-kgeselv<-kgesecl0<-evadiv<-kpofcr<-qerfiFetch<-opifch2<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main
, , alter system set events 'trace[sql_mon.*] [SQL: ...] disk=high,memory=high,get_time=highres';
, / real-time SQL (RTSM - Real Time SQL Monitor).
, , , , . - , .