Informative Error Messages
You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:
I was working on an external table today and fat fingered something. This is the error report-
Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!
Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!
Apparently some of the Oracle developers have a sense of humor.
By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:
SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause: User attempted on operation on an external table which is
// not supported.
// *Action: Don't do that!
There is one particular kind of humor that Oracle developers have been showing since about 9i that I don’t really appreciate.
The kind that writes this:
Fri Feb 4 22:41:06 2011
Cannot re-create tempfile /ublah/oracle/oradata/blah/temp03.dbf, the same name
file exists
Fri Feb 4 22:41:06 2011
in the alert log file and does NOT add an Oracle error code to it of the form
“ORA-xxxx”.
Basically it means searching for the general string “ORA-” to locate possible problems in this file is out of the question.
Thanks for nothing, developers: I now have to read the blessed thing every day looking for any of these messages, line by line…
Yeah, I understand grid/OEM/xml can do all that for me as well as the coffee.
But exactly and precisely how?
On an only-just-related note – Oracle documentation lacking clarity – I always like this quote from the 11gR1 Globalization Support Guide – http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch6unicode.htm#i1006807
“I think these secions that describe encoding are out of date. For example, do Java and Microsoft Windows still use UCS-2? Or do they use UTF-16? I think UNIX uses UTF-32. So I am confused about what is the most current information for these sections.”
Noons,
Don’t hold back, let us know how you really feel! 😉
Dominic,
Docs are full of interesting things – like the index entry back in some distant version that had:
“Dirty Reads – Done Dirt Cheap”
Kerry
:p
Sometimes the simplest ones confound the newbies. After awhile, we learn the eccentric nuances of Oracle error messages, and come to understand what they mean, which is basically: “that won’t work”. Here’s an elegant example:
SQL> select tablename from dba_tables;
select tablename from dba_tables
*
ERROR at line 1:
ORA-00904: “TABLENAME”: invalid identifier
SQL> !oerr ora 904
00904, 00000, “%s: invalid identifier”
// *Cause:
// *Action:
We all know what I did wrong, but imagine you’re just learning SQL or don’t use it every day. What action do you take?
I rest my case….
We just ran into another informative error message:
=> oerr ora 2298
02298, 00000,”cannot validate (%s.%s) – parent keys not found”
// *Cause: an alter table validating constraint failed because the table has
// orphaned child records.
// *Action: Obvious
Sounds like a teenager trying to borrow the family car while the folks are out of town.
“parent keys not found”