How To Read Oracle Error Stack
Contents |
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers a powerful and flexible exception architecture. Of course, there is always room for improvement, and in Oracle Database 10g, exception handling takes dbms_utility.format_error_backtrace example in oracle a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the how to find which line error was raised in oracle problem that this function solves and how best to use it. Who Raised That Exception? When an exception is raised, one of dbms_utility.format_call_stack in oracle the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go pl sql call stack unhandled. Let's revisit the error-handling behavior available to programmers in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results: ERROR at line 1:
Oracle Exception Stack Trace
ORA-01403: no data found ORA-06512: at "SCOTT.PROC1", line 4 ORA-06512: at "SCOTT.PROC2", line 6 ORA-06512: at "SCOTT.PROC3", line 4 ORA-06512: at line 3 Code Listing 1: A stack of procedures CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2(30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; / This is the error trace dump of an unhandled exception, and it shows that the error was raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions. Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of error logging). Here is the second version of proc3 : CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN my_putline ( DBMS_UTILITY.FORMAT_ERROR_STACK); END; / Notice that I call DBMS_UTILITY.FORMAT_ERROR_STACK , because it will return the full error message. Of course, DBMS_OUTPUT
TrainingOracle Scripts and ToolsSession SnapperSitemapRecent site activity Recent site activityOracle Performance Troubleshooting Trainingedited by Tanel PoderORADEBUG DOCedited by Tanel PoderHomeedited by Tanel PoderTroubleshooting Cursor Sharing (finding the reasons for no reuse
Oracle Call Stack Trace
of existing child cursors)removed by Tanel PoderTroubleshooting excessive logical IOs - oradebug dump errorstack 3 Oracle ASSM case studyremoved by Tanel PoderView All Copyright© 2009-2010 E2SN Pte Ltd.All rights reserved. Oracle format_error_stack vs format_error_backtrace Living Books > Oracle Troubleshooting > How to read ERRORSTACK output Errorstack tracefiles are very useful for troubleshooting ORA-600's, crashes, hangs and even just bad http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html performance.Errorstack dumps are dumped automatically by Oracle when critical errors such as ORA-600 happen. This is when you see an error reported in alert.log and the generated tracefile has a "ksedmp: internal or fatal error" string in its beginning, followed by the error code (usually ORA-7445 or ORA-600 with some parameters). "ksedmp" means Kernel http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output Service Error DuMP, so everything below that line is the errorstack dump.Errorstack dumps can also be manually invoked by issuing ORADEBUG ERRORSTACK 3 (when being connected to the target process using ORADEBUG SETOSPID). This can be useful when a session seems to be hung (but without showing a reasonable wait event in V$SESSION_WAIT) or is consuming much more resources than normally and you want to know which exact bind variable values are currently used for executing the SQL.Errorstack tracefile has a lot of information it, some more and some less useful for practical troubleshooting. The vast amount of data and the binary dumps may scare you away, but I hope to show that there's some well understandable structure in there.In this article we will go through following techniques:Contents1 Reading the current executing SQL statement text from errorstack trace2 Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace3 Reading the current bind vari
Oracle 11gR1 RAC to 11gR2 Applying PSU 11.2.0.3.5 to Grid Infrastructure and DB Home ASM Dataguard Install Transportable Tablespace Example Oracle12c Performance Grid MySQL Reset MySQL Root Password Unix Linux AIX HP-UX Solaris Netapp Reference Resource Center http://askdba.org/weblog/2008/04/errorstack-oracle-debugging-event/ ADRCI Starting with Oracle Goldengate Scripts Useful SQL for DBA's Useful Metalink Articles About https://oracle-base.com/articles/12c/utl-call-stack-12cr1 Us Contact ERRORSTACK- Oracle Debugging Event Amit Bansal / 17 April, 2008 One more post arising out of my frequent visits to Oracle forum 😉 Basically there was a question on "How to set events for ORA - 1652 errors" I had replied to the post but Aman asked me if I could provide any notes in oracle regarding Errorstack.I didn't had one so I informed him that I will be posting on my blog ( Cheap Publicity of my blog 🙂 ) Ok, this was the background to it, now coming to the main thing. Errorstack is used as debugging event for following reasons To extract Failing SQL statement To get Oracle Function call (to be used by Oracle) for interpreting cause for the error (e.g how to read ORA 4030, ORA- 4031). It is also used in case of database hang scenarios. Refer to my earlier article on database hang It also contains execution plan in some cases which can be used to diagnose ORA - 1652 errors (Excessive temp usage) Errorstack can be set at System level and also at Session level. e.g To set errorstack for ORA - 1652 ,Syntax will be alter system set events '1652 trace name errorstack level 3'; If suppose, you want to set it at session level, then alter session set events '1652 trace name errorstack level 3'; You can also use oradebug to take errorstack for particular process (say ospid 1234) as below sqlplus "/ as sysdba" oradebug setospid 1234 oradebug unlimit oradebug dump errorstack 3 There are different levels for the errorstack with level 3 being the highest. Note: There is no such level as level 10 or 12 which you might sometimes find on web. e.g event='1401 trace name errorstack, level 12' Extracted from http://www.orafaq.com/faqdbain.htm . Specifying this will default to level 3. Let's see one trace file for error ORA - 942 i.e "table or view does not exist" SYS>alter session set events '942 trace name errorstack level 3'; Session altered. SYS>select *
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » 12c » Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated. Call Stack Error Stack Backtrace Call Stack The call stack allows you to identify exactly where you are in the currently running code, which includes information about nesting of subprogram calls. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_CALL_STACK function, as shown below. -- Procedure to display the call stack. CREATE OR REPLACE PROCEDURE display_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / -- Test package to show a nested call. CREATE OR REPLACE PACKAGE test_pkg AS PROCEDURE proc_1; PROCEDURE proc_2; PROCEDURE proc_3; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS PROCEDURE proc_1 AS BEGIN proc_2; END; PROCEDURE proc_2 AS BEGIN proc_3; END; PROCEDURE proc_3 AS BEGIN display_call_stack; END; END; / -- Run the test. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** ----- PL/SQL Call Stack ----- object line object handle number name 0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK 0xb6d14298 15 package body TEST.TEST_PKG 0xb6d14298 10 package body TEST.TEST_PKG 0xb6d14298 5 package body TEST.TEST_PKG 0xb99fe7c8 1 anonymous block ***** Call Stack End ***** PL/SQL procedure successfully completed. SQL> As