Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use errorstack events for error tracking and diagnosis", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to use errorstack events for error tracking and diagnosis" this article.

Sometimes Oracle errors and exceptions occur after executing a series of background SQL or PL/SQL, and it is difficult to determine the location of the error only based on the ORA error number and simple messages. At this time, we can use errorstack events to help us locate the accurate error SQL, which can effectively help us to diagnose the problem.

Create a test table before the experiment

Conn scott/tiger

Create table T1 (id number, value number (2jin1))

Set up errorstack event tracking

Conn / as sysdba

Alter system set events' 1438 trace name errorstack forever, level 3'

In practice, you can manually execute the problematic background code to get the trace file. Here we simply execute a SQL to do the test.

Conn scott/tiger

Insert into T1 values (1, 100.2)

System error:

An error occurred on line 1:

ORA-01438: the value is greater than the allowable precision specified for this column

Turn off errorstack event tracking

Conn / as sysdba

Alter system set events' 1438 trace name errorstack off'

Open the warning log and you can see the settings for the event and the corresponding trace file

Sun May 20 20:12:45 2018

OS Pid: 3924 executed alter system set events' 1438 trace name errorstack forever, level 3'

Sun May 20 20:12:52 2018

Errors in file C:\ ORACLE\ diag\ rdbms\ mes\ mes\ trace\ mes_ora_1340.trc:

ORA-01438: the value is greater than the allowable precision specified for this column

Errors in file C:\ ORACLE\ diag\ rdbms\ mes\ mes\ trace\ mes_ora_1340.trc:

ORA-01438: the value is greater than the allowable precision specified for this column

Errors in file C:\ ORACLE\ diag\ rdbms\ mes\ mes\ trace\ mes_ora_1340.trc:

ORA-01438: the value is greater than the allowable precision specified for this column

Sun May 20 20:12:54 2018

Dumping diagnostic data in directory= [CDMP _ 20180520201254], requested by (instance=1, osid=1340), summary= [abnormal process termination].

Sun May 20 20:13:04 2018

OS Pid: 292 executed alter system set events' 1438 trace name errorstack off'

Looking at the contents of the trace file mes_ora_1340.trc, you can see the specific SQL information where the error occurred.

-Error Stack Dump-

ORA-01438: the value is greater than the allowable precision specified for this column

-Current SQL Statement for this session (sql_id=5pwbn8v653a76)-

Insert into T1 values (1, 100.2)

-Call Stack Trace-

Further down is the call information of the stack, which can be referred for further tracking.

After finding the SQL where the problem occurs, we can analyze and diagnose it pertinently.

The above is all the contents of the article "how to use errorstack events for error tracking and diagnosis". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.