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

Editor to share with you how to use Oracle ErrorStack, I hope you will get something after reading this article. Let's discuss it together.

I. Overview

In the process of running Oracle database, we often encounter errors of one kind or another, but the error prompts are not specific, which makes it more difficult for us to diagnose the problem.

ErrorStack is a method provided by Oracle to trace the error stack. By setting the trace, we can dump some background information of the specified error in detail and write it into the trace file to help us diagnose the problem.

Note:

1. When a key error occurs in oracle, such as: ora-600,Errorstack is automatically written to the trace file by oracle dump.

2. When you see this kind of error in alert.log, and prompt that the trace file has been generated. After opening the corresponding trace, you will find that this kind of trace file usually starts with "ksedmp:internal or fatal error", "kesdmp" means Kernel Service Error Dump, and the following line is the error stack of the errorstack record!

Errorstack dump can also be called manually using Oradebug errorstack 3, as long as the target process is set using Oradebug setospid first. Oradebug Errorstack for diagnosing a session seems to live in Hang (but there is no reasonable wait event in v$session_wait) or consumes more resources than normal, get the current session execution sql, specific variable values, and other information to help you find the root of the problem!

II. Tracking levels and methods

ErrorStack has four main trace levels, as follows

0 dump only the error stack

1 dump error stack and function call stack

2 Level 1 + ProcessState

3 Level 2 + Context area (usually we use this level of tracking when diagnosing problems! )

The ErrorStack setting method is as follows (only specify a specific error code, which can only be triggered when this specific error occurs! )

Instance level: alter system set events='984 trace name errorstack forever,level 3 'scope=spfile

Session level: alter session set events='984 trace name errorstack forever,level 3'

Oradebug: 1, oradebug setospid xxxx; 2, oradebug dump errorstack 3-statements currently running by session

Third, the contents of the ErrorStack tracking file

The Errorstack trace file has a lot of information, and here we mainly explain the four parts that are most useful for us in diagnosing problems (many other things we can't understand), as follows

Found that SQL text is currently being executed from the Errorstack trace file.

It is found from the Errorstack trace file that the PL/SQL package and PL/SQL source code line number are currently being executed.

The current bind variable value was found in the Errorstack trace file.

Find out how much private memory (UGA) a cursor is using from the Errorstack tracking file.

For the above four parts, I will use a specific errorstack trace file example to show the box to deepen understanding, errorstack trace file is as follows (the specific generation method code, put at the end). The content of this part mainly refers to tanelpoder Daniel's blog.

1. SQL text is currently being executed from the Errorstack trace file.

This section is very easy to find, and the text information of the current sql statement is at the top of the trace file (you can search for Current SQL statement for this session)

Trace file / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = / u01/oracle/product/11.2.0

System name: Linux

Node name: 192oracle.cn100.com

Release: 2.6.32-358.el6.x86_64

Version: # 1 SMP Fri Feb 22 00:31:26 UTC 2013

Machine: x86_64

Instance name: cn100

Redo thread mounted by this instance: 1

Oracle process number: 26

Unix process pid: 10848, image: oracle@192oracle.cn100.com (TNS V1-V3)

* 2014-07-01 11 16 purl 36.260

* SESSION ID: (61.13360) 2014-07-01 1115 1615 36.260

* CLIENT ID: () 2014-07-01 11 purl 1636.260

* SERVICE NAME: (SYS$USERS) 2014-07-01 1115 1615 36.260

* MODULE NAME: (SQL*Plus) 2014-07-01 1115 1615 36.260

* ACTION NAME: () 2014-07-01 11 purl 1636.260

DbkedDefDump (): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

-Error Stack Dump-

ORA-01438: value larger than specified precision allowed for this column

-Current SQL Statement for this session (sql_id=b8n03s73k7d39)-as you can see, the current SQL is just below this line

INSERT INTO DH_T VALUES (: B2,: B1)

-PL/SQL Stack-

-PL/SQL Call Stack-

Object line object

Handle number name

0x1075fcd10 6 procedure DBMON.P_DH1

0xfcfaebe8 7 procedure DBMON.P_DH2

0x10e7d6420 1 anonymous block

-Call Stack Trace-

Calling call entry argument values in hex

Location type point (? Means dubious value)

Skdstdst () + 36 call kgdsdst () 000000000? 000000000?

7FFF332C8AD8? 000000001?

7FFF332CCFD8? 000000000?

. For typesetting, follow-up omission.

2. Find that the PL/SQL package and PL/SQL source code line number are currently being executed from the Errorstack trace file

The Errorstack trace file is the same as before, as follows

Trace file / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = / u01/oracle/product/11.2.0

System name: Linux

Node name: 192oracle.cn100.com

Release: 2.6.32-358.el6.x86_64

Version: # 1 SMP Fri Feb 22 00:31:26 UTC 2013

Machine: x86_64

Instance name: cn100

Redo thread mounted by this instance: 1

Oracle process number: 26

Unix process pid: 10848, image: oracle@192oracle.cn100.com (TNS V1-V3)

* 2014-07-01 11 16 purl 36.260

* SESSION ID: (61.13360) 2014-07-01 1115 1615 36.260

* CLIENT ID: () 2014-07-01 11 purl 1636.260

* SERVICE NAME: (SYS$USERS) 2014-07-01 1115 1615 36.260

* MODULE NAME: (SQL*Plus) 2014-07-01 1115 1615 36.260

* ACTION NAME: () 2014-07-01 11 purl 1636.260

DbkedDefDump (): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

-Error Stack Dump-

ORA-01438: value larger than specified precision allowed for this column

-Current SQL Statement for this session (sql_id=b8n03s73k7d39)-as you can see, the current SQL is just below this line

INSERT INTO DH_T VALUES (: B2,: B1)

-PL/SQL Stack-

-PL/SQL Call Stack-

Object line object

Handle number name

0x1075fcd10 6 procedure DBMON.P_DH1

0xfcfaebe8 7 procedure DBMON.P_DH2

0x10e7d6420 1 anonymous block

-Call Stack Trace-

Calling call entry argument values in hex

Location type point (? Means dubious value)

Skdstdst () + 36 call kgdsdst () 000000000? 000000000?

7FFF332C8AD8? 000000001?

7FFF332CCFD8? 000000000?

. For typesetting, follow-up omission.

Note that the PL/SQL red part of the trace file above is what we are concerned about.

If the errorstack trace is performed and the trace process executes a PL/SQL call, then the PL/SQL call heap will also be tracked (in the PL/SQL Call Stack section). This section tells you which PL/SQL procedure (package or function) Oracle was executing when the error occurred and which call occurred during the errorstack trace. This is very helpful for us to diagnose the problem.

The PL/SQL Call Stack contains three columns, as follows

Object handle

Line number

Object name

Let's introduce the meaning of these three columns one by one:

1 、 object handle

Object handle is the memory address of this object (PL/SQL procedure, package, function, anonymous block) that is load into library cache. You can associate this mapping address with the X$KGLOB.KGLHDADR table column to find out which object is being processed. As follows

SQL > select kglnaown,kglnaobj,kglhdadr from X$KGLOB a where KGLHDADR='00000001075FCD10'

KGLNAOWN KGLNAOBJ KGLHDADR

DBMON P_DH1 00000001075FCD10

2 、 line number

This is very important information, and it will tell you the PL/SQL code that was executing when the errorstack call occurred (you can navigate to a specific line in the code). For example, in the output above, the DBMON.P_DH2 stored procedure is called on line 1 of the anonymous block, while the DBMON.P_DH2 stored procedure calls another stored procedure, DBMON.P_DH1, on line 7, and line 6 of the DBMON.P_DH2 stored procedure is being executed when the errorstack trace occurs.

3 、 object name

The name of the object stored in PL/SQL (or anonymous block, when the object is not stored in a procedure). If it is an anonymous block (the text of the anonymous block can be found through V$SQL), you can associate this address with V$SQL.ADDRESS to find the text information of the anonymous block.

The above PL/SQL call stack contains only three lines.

0x1075fcd10 6 procedure DBMON.P_DH1

0xfcfaebe8 7 procedure DBMON.P_DH2

0x10e7d6420 1 anonymous block

You should read a PL/SQL call stack from the bottom up, for example

1. The line at the bottom tells us that an anonymous block is being executed and that in the first line of the anonymous block, it is calling the DBMON.P_DH2 stored procedure

two。 The second line tells us that the DBMON.P_DH2 stored procedure called another stored procedure DBMON.P_DH1 on line 7.

Line 6 of the 3.DBMON.P_DH2 stored procedure has an error and the errorstack information is dumped.

By querying DBA_SOURCE, we can verify with part of the PL/SQL call stack information in the errorstack trace file, as follows.

SQL > select line, text from dba_source where owner = 'DBMON' and name =' Paddy DH2' order by line asc

LINE TEXT

1 procedure p_dh3 as

2 v_cnt number

3 begin

4-just for errorstack test

5 select count (*) into v_cnt from dh_t

6 dbms_output.put_line ('the dh_t count is' | | v_cnt)

7 p_dh2

8 end

nine

9 rows selected.

SQL > select line, text from dba_source where owner = 'DBMON' and name =' Please DH1' order by line asc

LINE TEXT

1 procedure p_dh2 as

2 v_id number: = 1234335

3 v_name varchar2: = 'oradh'

4 begin

5-- just for errorstack test

6 insert into dh_t values (vSecretidmage vandalism name)

7 commit

8 end

nine

9 rows selected.

You can find line 6 of PL/SQL that the session is executing (an insert statement causes an error).

Usually, when an error dump,crash,hang occurs (the top line is the code being executed by the "child" function that is recursively called by "parent" function), PL/SQL errorstack tells us the exact PL/SQL code.

3. Find the current bind variable value from the Errorstack trace file

Why do we need to look for specific binding variable values after finding specific statements? Can be summarized into the following four reasons

A session can become CPU-consuming in some way, and the wait wait time for the session doesn't make any sense.

You need to investigate what SQL is being executed, and you need to look at the binding variables that come with SQL

The execution plan of SQL is normal, but the performance is very low.

It can be assumed that when some tables or row sources become larger, there is a data skew, and CBO does not calculate the correct execution plan.

Therefore, you need to know what binding variables SQL uses when problems occur. Unfortunately, there is not a V$ view in Oracle for us to look at the current binding variable value of a session. The V$SQL_BIND_CAPTURE view only randomly samples the values of bound variables and does not store all the values of the bound variables used, while the values shown in dbms_xplan.display_cursor are only the values of the binding variables that were snooped for the first time.

The real-time SQL Monitoring feature in Oracle 11gR2 can achieve this purpose. There is a column of BIND_XML in the V$SQL_MONITOR that contains the bind variable values that has been running long enough (SQL that occupies more than 5s of CPU by default appears in the secondary view). But this is only valid if you have 11gR2 and have Diag+Tuning pack licenses.

Note: because the binding variable value of the SQL statement exists in the private memory of the process's PGA, it is not easy to track the private memory of another process. The errorstack trace file contains CURSORDUMP, which contains the bind variable value we want.

Let's move on to the initial trace file, as follows

Trace file / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = / u01/oracle/product/11.2.0

System name: Linux

Node name: 192oracle.cn100.com

Release: 2.6.32-358.el6.x86_64

Version: # 1 SMP Fri Feb 22 00:31:26 UTC 2013

Machine: x86_64

Instance name: cn100

Redo thread mounted by this instance: 1

Oracle process number: 26

Unix process pid: 10848, image: oracle@192oracle.cn100.com (TNS V1-V3)

* 2014-07-01 11 16 purl 36.260

* SESSION ID: (61.13360) 2014-07-01 1115 1615 36.260

* CLIENT ID: () 2014-07-01 11 purl 1636.260

* SERVICE NAME: (SYS$USERS) 2014-07-01 1115 1615 36.260

* MODULE NAME: (SQL*Plus) 2014-07-01 1115 1615 36.260

* ACTION NAME: () 2014-07-01 11 purl 1636.260

DbkedDefDump (): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

-Error Stack Dump-

ORA-01438: value larger than specified precision allowed for this column

-Current SQL Statement for this session (sql_id=b8n03s73k7d39)-as you can see, the current SQL is just below this line

INSERT INTO DH_T VALUES (: B2,: B1)

-PL/SQL Stack-

-PL/SQL Call Stack-

Object line object

Handle number name

0x1075fcd10 6 procedure DBMON.P_DH1

0xfcfaebe8 7 procedure DBMON.P_DH2

0x10e7d6420 1 anonymous block

-Call Stack Trace-

Calling call entry argument values in hex

Location type point (? Means dubious value)

Skdstdst () + 36 call kgdsdst () 000000000? 000000000?

7FFF332C8AD8? 000000001?

7FFF332CCFD8? 000000000?

. For typesetting, follow-up omission.

When you open a trace file, usually the first step is to search for the first "Session Cursor Dump". When you search for it, you will see the following output

-Session Cursor Dump-

Current cursor: 2, pgadep=1

Open cursors (pls, sys, hwm, max): 3 (1,1,64, 1000)

NULL=0 SYNTAX=0 PARSE=0 BOUND=3 FETCH=0 ROW=0

Cached frame pages (total, free):

4k (11,8), 8k (0,0), 16k (0,0), 32k (0,0)

-Current Cursor-

Xsc=0x7f5227898580 ctx=0xf92d7aa8 pgactx=0xf92d7aa8 ctxcbk=0xf92d74f8 ctxqbc= (nil) ctxrws=0x10293a4c0

-Explain Plan Dump-

-Compact Format (Stream)-

Dumping stream from 0 to 22

-

0000: 143 137 23 1 10 105 000 10 000 21 9 4 6 7 .i.

0020: 0 142..

-Plan Table-

=

Plan Table

=

-+-+

| | Id | Operation | Name | Rows | Bytes | Cost | Time | |

-+-+

| | 0 | INSERT STATEMENT | 1 | | |

| | 1 | LOAD TABLE CONVENTIONAL | | |

-+-+

Content of other_xml column

= =

Db_version: 11.2.0.1

Parse_schema: DBMON

Plan_hash: 0

Plan_hash_2: 0

Compilation Environment Dump

Optimizer_mode_hinted = false

Optimizer_features_hinted = 0.0.0

Parallel_execution_enabled = true

Parallel_query_forced_dop = 0

. For typesetting, follow-up omission.

We can find the following useful content

Current cursor: 2, which tells us that in the cursor opened by UGA in this session, the Cursor# 6 cursor is currently being executed

The the pgadep:1 variable tells us PGA depth, that is, the depth of recursion that this query executes.

If pgadep is 0, it means that the query is a top-level query that is being executed by the user or application through the OCI interface.

A pgadep of 1 means that it is a recursive query that is executed through a recursive program interface (RPI), either a data dictionary query or a SQL simply executed through a PL/SQL call.

So, to find the value of the binding variable for the current query that is currently being executed, all we need to do is search forward in trace file for Cursor2#, as follows

Note: this search term is case sensitive.

Cursor#2 (0x7f5227951aa0) state=BOUND curiob=0x7f5227898580

Curflg=cd fl2=0 par= (nil) ses=0x129a8edc0

-Dump Cursor sql_id=b8n03s73k7d39 xsc=0x7f5227898580 cur=0x7f5227951aa0-

LibraryHandle: Address=f68ce8d8 Hash=c723b469 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

ObjectName: Name=INSERT INTO DH_T VALUES (: B2,: B1)

FullHashValue=38f6bac85b76f427b45003c1c723b469 Namespace=SQL AREA (00) Type=CURSOR (00) Identifier=3341005929 OwnerIdn=148

Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=1 TotalLockCount=1 TotalPinCount=1

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0

Concurrency: DependencyMutex=f68ce988 (0,1,0,0) Mutex=f68cea00 (61,19,0,6)

Flags=RON/PIN/TIM/PN0/DBN/ [10012841]

WaitersLists:

Lock=f68ce968 [f68ce968,f68ce968]

Pin=f68ce978 [f68ce948,f68ce948]

Timestamp: Current=07-01-2014 11:16:35

LibraryObject: Address=f6b87d18 HeapMask=0000-0001-0001 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000]

ChildTable: size='16'

Child: id='0' Table=f6b88bc8 Reference=f6b88668 Handle=fab7b4c0

Children:

Child: childNum='0'

LibraryHandle: Address=fab7b4c0 Hash=0 LockMode=N PinMode=S LoadLockMode=0 Status=VALD

Name: Namespace=SQL AREA (00) Type=CURSOR (00)

Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=0 HandleInUse=0

Concurrency: DependencyMutex=fab7b570 (0,0,0,0) Mutex=f68cea00 (61,19,0,6)

Flags=RON/PIN/PN0/EXP/ [10012111]

WaitersLists:

Lock=fab7b550 [fab7b550,fab7b550]

Pin=fab7b560 [fab7b530,fab7b530]

LibraryObject: Address=f6569b20 HeapMask=0000-0001-0001 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000]

DataBlocks:

Block: # ='0' name= CCR ^ c723b469 pins=0 Change=NONE

Heap=f6a0b38 Pointer=f6569c08 Extent=f6569aa0 Flags=I/-/P/A/-/-

FreedLocation=0 Alloc=3.093750 Size=3.937500 LoadTime=11473268840

Block: # ='6' name= SQL ^ c723b469 pins=0 Change=NONE

Heap=f6b88438 Pointer=f92d7aa8 Extent=f92d6e48 Flags=I/-/P/A/-/E

FreedLocation=0 Alloc=8.890625 Size=11.859375 LoadTime=0

NamespaceDump:

Child Cursor: Heap0=0xf6569c08 Heap6=0xf92d7aa8 Heap0 Load Time=07-01-2014 11:16:35 Heap6 Load Time=07-01-2014 11:16:35

NamespaceDump:

Parent Cursor: sql_id=b8n03s73k7d39 parent=0xf6b87e00 maxchild=1 plk=y ppn=n kkscs=0xf6b88308 nxt= (nil) flg=18 cld=0 hd=0xfab7b4c0 par=0xf6b87e00

Mutex 0xf6b88308 (0,0) idn 3000000000

Ct=0 hsh=0 unp= (nil) unn=0 hvl=f6b88c60 nhv=0 ses= (nil)

Hep=0xf6b88398 flg=80 ld=1 ob=0xf6569b20 ptr=0xf92d7aa8 fex=0xf92d6e48

Cursor instantiation=0x7f5227898580 used=1404184595 exec_id=16777216 exec=1

Child#0 (0xfab7b4c0) pcs=0xf6b88308

Clk=0x10d6111e0 ci=0xf6569c08 pn=0xfdf4c890 ctx=0xf92d7aa8

Kgsccflg=1 llk [0x7f5227898588,0x7f5227898588] idx=6a

Xscflg=c0110676 fl2=d120000 fl3=422a2188 fl4=100

-Bind Byte Code (IN)-

Opcode = 6 Bind Rpi Scalar Sql In (may be out) Nocopy NoSkip

Offsi = 48, Offsi = 0

Opcode = 6 Bind Rpi Scalar Sql In (may be out) Nocopy NoSkip

Offsi = 48, Offsi = 32

-Bind Info (kkscoacd)-

Bind#0

Oacdty=02 mxl=22 (21) mxlc=00 mal=00 scl=00 pre=00

Oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0

Kxsbbbfp=7f5227943d08 bln=22 avl=05 flg=09

Value=1234335

Bind#1

Oacdty=01 mxl=2000 (200) mxlc=00 mal=00 scl=00 pre=00

Oacflg=13 fl2=206001 frm=01 csi=852 siz=2000 off=0

Kxsbbbfp=7f5227943d48 bln=2000 avl=05 flg=09

Value= "oradh"

Frames pfr 0x7f5227897c18 siz=3424 efr 0x7f5227897b38 siz=3376

Cursor frame dump

Enxt: 3.0x00000550 enxt: 2.0x00000040 enxt: 1.0x000007a0

Pnxt: 1.0x00000030

Kxscphp=0x7f5227961030 siz=984 inu=584 nps=360

Kxscbhp=0x7f5227961920 siz=984 inu=152 nps=0

Kxscwhp=0x7f5227960f40 siz=4056 inu=56 nps=0

Note: focus on the red font above

ErrorStack dump is a reliable way to find the values of binding variables currently used in SQL statements. In addition, there are the following instructions:

Found in the errorstack trace file is Cursor#2, its state=BOUND, which means that the value of the binding variable has been assigned to the

The SQL text is what we looked up earlier, and we can see the values of two binding variables

Bind variale numbering starts at 0, so if we want to search for the first bound value, we need to search Bind#0

4. Find out how many private memory (UGA) a cursor is using from the Errorstack trace file.

Continuing our previous example, the trace file can measure how much UGA private memory is used by some cursors of an session. As follows

Cursor#2 cursor section:

Cursor frame dump

Enxt: 3.0x00000550 enxt: 2.0x00000040 enxt: 1.0x000007a0

Pnxt: 1.0x00000030

Kxscphp=0x7f5227961030 siz=984 inu=584 nps=360

Kxscbhp=0x7f5227961920 siz=984 inu=152 nps=0

Kxscwhp=0x7f5227960f40 siz=4056 inu=56 nps=0

Cursor#1 cursor section:

Cursor frame dump

Enxt: 12.0x000005c0 enxt: 11.0x00000fd8 enxt: 10.0x00000fa0 enxt: 9.0x00000658

Enxt: 8.0x00000228 enxt: 7.0x00000fd8 enxt: 6.0x00000fa0 enxt: 5.0x00000248

Enxt: 4.0x00000fa0 enxt: 3.0x00000410 enxt: 2.0x00000480 enxt: 1.0x00000f70

Pnxt: 1.0x00000030

Kxscphp=0x7f5227960c70 siz=2792 inu=1056 nps=424

Kxscbhp=0x7f5227960e50 siz=10376 inu=10008 nps=8768

In the cursor frame dump section we can see some information about the start of kxsc, which means Kernel eXECUTION Shared Cursor, and the siz parameter for each line tells us the amount of private memory currently allocated. Of course, we need to summarize all the siz, and this value is the memory usage of the cursor.

Here is a summary of some of my guesses:

01.Heap description Meaning

02.kxscphp Cursor permanent heap. Allocated when cursor is opened

03.kxscdfhp Cursor default heap-default duration allocations

04.kxscehp Cursor ephemeral heap-short lived duration allocations

05.kxscwhp Cursor Work heap-used when actually executing the cursor (workareas etc)

06.kxscbhp Cursor Bind heap-this is where bind variable values and their metadata are kept.

IV. Experimental code

1. Create tables and PL/SQL functions

Create table dh_t (id number (2), name varchar2 (2))

Create or replace procedure p_dh2 as

V_id number: = 1234335

V_name varchar2: = 'oradh'

Begin

-- just for errorstack test

Insert into dh_t values (vSecretid.vandalism name)

Commit

End

/

Create or replace procedure p_dh3 as

V_cnt number

Begin

-just for errorstack test

Select count (*) into v_cnt from dh_t

Dbms_output.put_line ('the dh_t count is' | | v_cnt)

P_dh2

End

/

2. Errorstack tracking

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 1 11:15:52 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

SQL > set linesize 200 pagesize 999

SQL > col tracefile format A100

SQL > select spid,tracefile from v$process a where addr= (select paddr from v$session where sid= (select sid from v$mystat where rownum=1))

SPID TRACEFILE

--

10848 / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

SQL > alter session set events='1438 trace name errorstack forever,level 3'

Session altered.

SQL > exec p_dh3

BEGIN pumped dh3; END

*

ERROR at line 1:

ORA-01438: value larger than specified precision allowed for this column

ORA-06512: at "DBMON.P_DH1", line 6

ORA-06512: at "DBMON.P_DH2", line 7

ORA-06512: at line 1

[oracle@192oracle ~] $ls-ltr / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

-rw-r- 1 oracle oinstall 12190347 Jul 1 11:16 / u01/oracle/diag/rdbms/cn100/cn100/trace/cn100_ora_10848.trc

After reading this article, I believe you have a certain understanding of "how to use Oracle ErrorStack". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.