The 40 Most Common Oracle Errors

I have been in this industry long enough to remember the bad old days, when every developer and DBA had a pile of dusty Oracle user guides on their desks and, every time their database or application coughed up an error, would thumb through the books with mounting desperation. Fortunately, the times have moved on and we now all outsource… Continue Reading →

I have been in this industry long enough to remember the bad old days, when every developer and DBA had a pile of dusty Oracle user guides on their desks and, every time their database or application coughed up an error, would thumb through the books with mounting desperation. Fortunately, the times have moved on and we now all outsource our memories to Google and Bing and Baidu. Our desks are clear, but our need for external help whenever we are ambushed by unexpected errors is just as strong.

Using tools Google have made available, I have put together a list of the ORA messages we collectively searched for the most in the first month of 2012. These messages (see a Billboard-style Top 40 Chart at the bottom of this article) are a mix of those generic errors that can mean pretty much anything, the error messages we see most rarely, and those common error numbers that we never seem able to memorise.

ORA-12154: TNS:could not resolve the connect identifier specified (#1)

Coming in at number 1, and googled more than twice as often as any other error message, this error is, almost appropriately, often the very first one you get on trying to establish a connection to your database. Simply put, it means that you have named the database you wish to be connected to and Oracle doesn’t know who the heck you’re talking about.

This error is often caused by typos, maybe in your connection string, but possibly in your tnsnames.ora file. It is also possible that the tnsnames.ora file is not accessible or does not even exist. If it does, ensure that it contains the service name you are using; also go over it with a fine toothcomb to make sure it doesn’t have any unpaired parentheses or such.

ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s] (#2)

Coming in at number 2 is a generic error; it means something serious has gone wrong and you are going to need to roll up your sleeves, dig in, and find out what. But you are not without clues. Your alert.log file will contain the path to your trace file. You will want to look in it, as a record is written to the trace file every time an ORA-00600 error occurs. Take the information you get from your trace file and the first argument in the square brackets (the internal message number), and head over to My Oracle Support (Metalink). There you will find a handy ORA-0600 lookup tool (Note 153788.1) that will direct you to additional information that will help you solve your problem.

ORA-1722: Invalid Number (#3)

You get this error when your SQL tries to convert a non-numeric string into a number. This conversion might be explicit – to_number(‘I am looking for trouble’) – or implicit.

This error often arises when you have a table with a varchar2 column in which you store nothing but numbers. You know that this is bad practice, but you know you will get away with it as long as you strictly store nothing but numbers in the column and all your procedures treat the contents of the column as numeric. But then, one day, you hire a new developer and, seeing that the column is varchar2, he inserts a ‘two’ – instead of 2 – into it. And suddenly all the procedures and functions that reference this column clutch their chests and die.

ORA-03113: end-of-file on communication channel (#5)

This error pretty much means that your connection has clutched its chest and died. For some reason, your client machine and the database server are acting like an old married couple and have stopped talking to each other. That reason could be one of a rather long list: has your server crashed? has someone pulled out your network cable? was your process killed at the O/S level? is your Windows log full? or maybe there is an Oracle internal error?

My advice is this: do not overlook the obvious. This error is sometimes caused by the simplest of things. If, however, it is caused by an Oracle internal error, look to your alert log for further information.

ORA-01000: maximum open cursors exceeded (#23)

You get this error when a user of a host program attempts to open more cursors than they are allowed. The number of cursors allowed is dictated by the OPEN_CURSORS initialization parameter, and this quota can be eaten up by both implicit and explicit cursors. If you run into this error, there is a possibility that there is a bug in your application. Perhaps you’ve got an open cursor statement within a loop and you do not have a matching close cursor, and as a result your code is bleeding cursors all over the place.

However, it is possible that the OPEN_CURSORS number is just too low for the needs of your application and has to be upped. The default value is 50; however, the only factor limiting how high this number can go – 300, 1000, 2000 even – is what the operating system can take. However, it may be unwise to choose to change the OPEN_CURSORS parameter too steeply, rather than examine your code for leaks. That’ll be like thinking you can save yourself from drowning by drinking the sea.

Conclusion

I am not going to be able to analyse all of the Top 40 error messages given the constraints of space. Please inspect the list below (it’s interesting to see what we all search for the most anyway) and if you see an error that you feel you can provide succinct advice for, please add it to the comments section.

Top 40 Most Searched For Oracle Errors

Position Error Global Monthly Searches*
1 ORA-12154 101,500
2 ORA-00600 40,500
3 ORA-01722 27,100
4 ORA-12560 22,200
5 ORA-03113 18,100
6 ORA-00604 14,800
7 ORA-00936 12,100
8 ORA-01017 12,100
9 ORA-01555 12,100
10 ORA-04031 12,100
11 ORA-00257 12,100
12 ORA-27101 12,100
13 ORA-00911 12,100
14 ORA-00933 9,900
15 ORA-01403 9,900
16 ORA-01422 9,900
17 ORA-04030 9,900
18 ORA-00932 9,900
19 ORA-01031 8,100
20 ORA-20000 8,100
21 ORA-12560 8,100
22 ORA-06508 8,100
23 ORA-01000 6,600
24 ORA-12505 6,600
25 ORA-20001 6,600
26 ORA-12519 6,600
27 ORA-01008 6,600
28 ORA-00054 6,600
29 ORA-01830 6,600
30 ORA-00907 6,600
31 ORA-00984 6,600
32 ORA-01461 5,400
33 ORA-01110 5,400
34 ORA-00001 5,400
35 ORA-02010 5,400
36 ORA-12537 5,400
37 ORA-03135 5,400
38 ORA-01034 5,400
39 ORA-00918 5,400
40 ORA-04063 5,400

*Figures based on Google Adwords Keyword tool global monthly search average.