Files - Expert Oracle Database Architecture - page 98

Lastly, before we get to the actual error, we can see the session ID (19 in this example), session serial number
(27995 in this example), and related date/time information (all releases) as further identifying information:
*** SESSION ID:(19.27995) 2010-01-20 14:32:39.988
Now we are ready to get into the error itself:
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12410], [], [], [], [], [], [], []
Current SQL statement for this session:
select count(x) over ()
from t
----- Call Stack Trace -----
_ksedmp+524
_ksfdmp.160+14
_kgeriv+139
_kgesiv+78
_ksesic0+59
_qerixAllocate+4155
_qknRwsAllocateTree+281
_qknRwsAllocateTree+252
_qknRwsAllocateTree+252
_qknRwsAllocateTree+252
_qknDoRwsAllocate+9
...
Here we see a couple of important pieces of information. First, we find the SQL statement that was executing
when the internal error was raised, which is very useful for tracking down what application(s) was affected. Also, since
we see the SQL here, we can start investigating possible workarounds—trying different ways to code the SQL see if we
can quickly work around the issue while working on the bug. Furthermore, we can cut and paste the offending SQL
into SQL*Plus and see if we have a nicely reproducible test case for Oracle Support (these are the best kinds of test
cases, of course).
The other important pieces of information are the error code (typically 600, 3113, or 7445) and other arguments
associated with the error code. Using these, along with some of the stack trace information that shows the set of
Oracle internal subroutines that were called in order, we might be able to find an existing bug (and workarounds,
patches, and so on). For example, we might use the search string
ora-00600 12410 ksesic0 qerixAllocate qknRwsAllocateTree
Using My Oracle Support's advanced search (using all of the words, search the bug database), we immediately
find the bug 3800614 , “ORA-600 [12410] ON SIMPLE QUERY WITH ANALYTIC FUNCTION”. If we go to
http://support.oracle.com and search using that text, we will discover this bug, see that it is fixed in the next
release, and note that patches are available—all of this information is available to us. I often find that the error
I receive is one that has happened before and there are fixes or workarounds for it.
Trace File Wrap-up
You now know the two types of general trace files, where they are located, and how to find them. Hopefully you'll use
trace files mostly for tuning and increasing the performance of your application, rather than for filing service requests.
As a last note, Oracle Support does have access to many undocumented “events” that are very useful for dumping
out tons of diagnostic information whenever the database hits any error. For example, if you are getting an ORA-01555