Oracle buildings...

I used to hate Oracle Database (and other Oracle products too) because of it is much more complicated / heavyweight / slow / buggy and full of useless and sometime harmful stuff than needed. Also when you install you can’t remove it without leaving tons of zombie files around, breaking your JVM/Apache/Windows/etc. After an Oracle installation, your system will never be as before. And hey, have you ever seen an “universal installer” more pathetic than the oracle one?

But I see they are finally realizing that, so they released a “no-frills edition” of their database (160MB sized download, 700MB of memory used on my laptop, 1.2GB of disk space. “no frills”? wow!) that can be freely used for development and production. With native installer (happy to see that installer in the garbage)

The good news is that “Oracle Database XE is created from the same code base as the Oracle Database 10g product line”, so that you still have – for free! – the same usual Oracle buggy stuff (“varchar 2, the revenge!”, empty string == null, blob/clob limitation to 4k, etc), bugs, and nice useless error messages like

  • “ORA-00020: Maximum number of processes (%s) exceeded” (notice the %s)
  • “ORA-00904: invalid column name” (now guess which one!)
  • “ORA-00942: table or view does not exist” (when you join 96 tables in a query)
  • “ORA-00911: invalid character” (when your query is 1 billion character long…)
  • “ORA-01722: invalid number” (maybe I typed slevin?)
  • …and much more!

But now you have additional bugs due to the “no-frills edition”.

For example this one: when you use your poor Oracle connected application, after some time of activity you get “ORA-12519, TNS:no appropriate service handler found”, and you don’t get any more connection from the db, with SQLExceptions instead. The quick way to solve, is to restart the listener. Not bad uh, for a “proven industry database”, isn’t that?

Googling around I found that this is a bug on how OracleXE monitors processes, and issuing the following command at SQL command line will fix it (after restarting the listener): “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;”.
Worked for me. This saved me to install Oracle Enterprise on my laptop.

So, let’s run to download this new gem from Oracle and have fun!


75 Responses to “ORA-12519, TNS:no appropriate service handler found”  

  1. 1 Mikael Gueck

    What are you smoking? I use CLOBs larger than 4k every day. It used to be that you had to handle CLOBs using the standard JDBC Clob type like all JDBC manuals tell you to, but these days the convenience direct read as String works too.

  2. 2 Luigi

    :-)

    Mikael, check the link

  3. 3 James Horsley

    Thanks for the “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;” fix, I found your blog before the other forums page and was very happy to have my unit tests running again. Got to love the spurious oracle weirdness :)

  4. 4 Dharmesh

    I found your page after a lot of searching. How Oracle can do this? I wasted almost a day in searching for this solution to this issue.

  5. 5 Xamul

    Mikael, it works for you because you use a patched driver without knowing it ;-)

  6. 6 Shree Menon (its a Bug's life)

    I stumbled on your blog when I searched “see number of processes in oracle xe”. I been trying to figure out why this is happening and even though I got lot information from other sites (over last 3 days) nothing gave me more relief than to see my problems termed as “BUG(s)”. So its not only Microsoft Monopoly on Bugs. I also got my application running back thanks to your suggestion of “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;”

    Just for Information I was using ASP.NET 2.0 and ODP.Net (2.111.5.10)

    Thanks a lot Buddy. It might be that streets in Dallas are getting flooded but I am relaxed now.

  7. 7 Doug Matthews

    a big THANK YOU for this post, it also saved me from the hell that is Oracle’s confusing error messages! ;-)

  8. 8 Rene

    Thank you soooooooo much. If I would not have found your fix it would have cost me days. I’ll spend you a few beers, if you are in zurich once.

  9. 9 Giovanni

    Hello Luigi,

    friendsforfree brought me here…it’s 8:30PM and finally I have been able to install CS (you know what I am talking about…).

    Many thanks for this…and I am sure I will become a frequent reader of your blog!

    P.S. What about arranging a beer party someday with friendsforfree in duss? :-)

    Cheers,
    Giovanni

  10. 10 dbg

    Thanks for the TNS workaround, will buy you a beer if you make it to Geneva!

  11. 11 Ken

    Thanks for this, just spent two days fixing after a corrupt database and had to do a complete reinstall of OracleXE and got very frustrated with the ORA-12519 error.

    Going to take some headache tablets now and go home :o)

  12. 12 Richard

    Thanks soo much…

    You have saved me lots of time.

    I appreciate your blog entry.

    Regards,
    Richard
    India

  13. 13 tibi

    amazing that an product with so much money behind it can have a stupid bug in there product for so many months!!

    thanks and nice to read

  14. 14 Andree

    Hi

    I had the same problem and find a quite easy solution: Just use less Connections. So if you instantiate the Connection in a class, lett other classes using a db connection know this class. Add a method like getConnection() to give a reference to the connection to every class needing a db connection. than you create statements, close them and so on, but don’t close the connection! That way heavy traffic, many statements or multithreading won’t be a problem any more.

    Regards

  15. 15 Sean

    I was also getting the “ORA-12519, TNS:no appropriate service handler found” error with Oracle Express. Your fix worked great for me. Thanks for helping me solve this.

  16. 16 Luigi

    Happy to be helpful to you!

  17. 17 Grupo MightyKhronos

    Obrigado, você salvou nosso tcc =D

  18. 18 Michael Hardy

    Greetings,
    I obtained this error today after an update of NetBeans to version 6.5. Once I added the ojdbc jar to the lib directory and restarted GlassFish, the error went away.
    -Michael

  19. 19 siavash

    Thanks Luigi for the post, now I can run my unit tests.
    Thanks google for showing this post on top of the search results for TNS:no appropriate service handler found
    Thanks oracle for having the bug in the first place

  20. 20 eldani

    Great job man! Thank you very much!

  21. 21 John

    OMG!!! i lost the whole day for this unkown bug, and your solution was the answer….i keept looking at my code becose i moved the data base… and the code was fine…. its was oracle O_O!!! DAMM thxs

  22. 22 Danai SAE-HAN

    Hi!

    Your blog post has helped me a lot. Thanks!
    Now I can still use Oracle 10g XE within some VMware images instead of having to resort to a full blown Oracle 10g.


    Danai

  23. 23 JP

    Worked great!
    Probably saved me a day or two of running around completely mad, throwing things by the window, getting mad at my wife, thus eventually divorced, probably alcoholic, out of job, etc…
    Saved my life, really

    Thanks!
    –JP

  24. 24 Andrey

    Good sense of humor and a great fix!

    P.S. – Oracle does suck.

  25. 25 Rajan

    Thanks Buddy.. This post was very useful. Solved my issue right away !!
    Thanks again !

  26. 26 atomax72

    ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;

    don’t worked for me
    I’m running a java program in witch I open and close connection
    sometimes, when I try opening one the last was not steel closer and the listener refuses to connect
    I put a cicle for sleeping before opening and all work

    the solution is to use one connection only (reusing it) as suggested by Andree implementing a getConnection metod and reusing it till the end

  27. 27 Luigi

    A connection pool could help reusing the connections, and keeping their number low even if you open/close the connection every time.

  28. 28 Nishant

    Im also facing the problem
    “Failed to connect to database: ORA-12519: TNS:no appropriate service handler found (DBD ERROR: OCIServerAttach).”

    this problem started recently after we created a new pfile to start up database.
    Then we created new spfile also.

    As per the comments above I checked my parameter list

    both Spfile and Pfile is showing
    processes=150

    Do I again need to run the query “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;” ?

  29. 29 Luigi

    I don’t think you need. BTW, if you get the problem, you know how to solve it.

  30. 30 Nishant

    Nooo.. i dont know how to solve it …

    I will copy the message from Alert file below.

    Alert file
    =========
    Mon May 04 11:44:05 2009
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Process q002 started up but failed with error = 20
    Mon May 04 11:49:18 2009
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Process q005 started up but failed with error = 20
    Mon May 04 11:51:02 2009
    Private_strands 7 at log switch
    Thread 1 advanced to log sequence 44692
    Current log# 3 seq# 44692 mem# 0: D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCLJDE\REDO03.LOG
    Mon May 04 11:52:31 2009
    Errors in file d:\oracle\product\10.1.0\admin\orcljde\udump\orcljde_ora_3800.trc:
    ORA-25254: time-out in LISTEN while waiting for a message
    ORA-06512: at “SYS.DBMS_AQ”, line 577
    ORA-06512: at “SYSMAN.EMD_NOTIFICATION”, line 492
    ORA-06512: at line 1
    ————————————————————————————————

    When I checked individual trace file it shows as below
    =====================================

    Dump file d:\oracle\product\10.1.0\admin\orcljde\bdump\orcljde_ora_4392.trc
    Mon May 04 11:02:28 2009
    ORACLE V10.1.0.2.0 – Production vsnsta=0
    vsnsql=13 vsnxtr=3
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
    With the Partitioning, OLAP and Data Mining options
    Windows Server 2003 Version V5.2 Service Pack 2
    CPU : 4 – type 586, 2 Physical Cores
    Process Affinity: 0x00000000
    Memory (A/P) : PH:20392M/24575M, PG:27290M/32151M, VA:940M/2047M
    Instance name: orcljde

    Redo thread mounted by this instance: 1

    Oracle process number: 0

    4392

    Died during process startup with error 20 (seq=1464)
    OPIRIP: Uncaught error 20. Error stack:
    ORA-00020: maximum number of processes (150) exceeded

  31. 31 Luigi

    You may try to increase the number of processes further (200 or 250 or more), or call the Oracle support. This issue is an old bug on how Oracle is tracing the processes; so it may have a fix now, hopefully.

  32. 32 mhchai

    Hi

    You save my life.. I have been spending days and nights just trying to figure out what’s wrong. I tried many different approached but end up failed.

    Thank you so much.

    Thank Oracle for giving me such a nightmare experience.

  33. 33 krishna

    Thanks Buddy, that was a great tip!!!!!!

  34. 34 Oracle

    Nice! Couldn’t have figured it out myself.

  35. 35 Jason

    Thanks, this saved me days of frustration, too. Oracle: so much to hate, so much to pay for the privilege of hating it properly.

  36. 36 TheKing

    Hi,

    Im a db qa, i was facing this ORA-12519 error when ever i build a Oracle base, your blog really helps me in fixing this issue without contacting my devl….

  37. 37 Gordon

    Beautiful fix – worked like a charm!

  38. 38 LazyCoder

    Thanks mate, works like a charm, I was worried why I was getting that exception until I landed up on your blog.

    Cheers,
    LC

  39. 39 stephen Coughlan

    next time your in bucharest, ill buy you some beers too :)

  40. 40 JavaGuy

    Works like a champ! Thanks buddy!

  41. 41 DaveR

    The logs did not show the real problem. Once I ran the command, the app server “magically” came to life. I was about to re-install the whole thing. Thanks for the tip.

  42. 42 ipolevoy

    Thanks for this post. I implemented the change a bit differently:
    Edited file:

    /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/init.ora

    added this at the bottom of file:
    processes = 150
    SCOPE = SPFILE

    I then restarted Oracle, and the problem was gone.
    I second that Oracle has just about every default set to some wrong value, and it takes hours/days to get it behave. Coming from MySQL, thi sis really annoying.

    cheers

  43. 43 melih

    Thanks buddy, it solves the my problem ;)

  44. 44 Richard in Cambridge

    Hi Guys,
    Thanks to everyone who has contributed to this blog – you have saved me a great deal of heartache!
    Luigi – it looks like you can do a ‘pub crawl’ around the world collecting your free beers! You can have one in Cambridge (UK) too!

  45. 45 peter43

    Uff! Now my connection pool is working!

    Next time in Berlin, …

  46. 46 Zahid Rahim

    Hi my spfile already show 150 processes but i still get this error periodically and this error disappears automatically as well.

    can i increase those to around 200 or executing 150 processes statement again will stop this error appearing again??

  47. 47 Luigi

    Zahid, you can do that. It’s an Oracle bug, this is just a workaround, until Oracle decides to fix it.

  48. 48 Zahid Rahim

    Thanx Luiqi,

    I am going to increase process to 200.

    Well sorry can’t offer you a beer as i am muslim :P, But can offer you something else if u ever visit Lahore, Pakistan

  49. 49 Miiklay

    Unbelievable. So here I am in 2010 reading a blog post from 2007, and sure enough the fix works. I thought surely this wouldn’t be applicable 3 years later, because surely the bug/fix/ridiculous incompetence by Oracle would be fixed. Right? Wrong.

    Thank you very much.

  50. 50 Dinesh

    Thanks a lot. I had to increase to 450 processes before my issue went away, because I an inserting a whole bunch of rows using OpenJPA and why a separate process is spawned for every insert is beyond me.

  51. 51 Rajkumar Gundu

    thank you for the tip. it helped me.

  52. 52 ...

    When you run out ouf hand(ler)s, you can of course attack new arms and hands oooor you can alternately use connection pooling and use the same two hands you already have to get through your life.

    Opening connections for every bowel movement in your java classes isn’t the answer! Repent! .. *sigh*

  53. 53 Luigi

    Of course I was already using a connection pool, as probably all the above people. The problem above occurs even if you use a connection pool. As written, it’s a bug on how oracle manages and monitors processes. When Oracle runs out of connections you get a different error btw, and this is not the case

  54. 54 suresh

    Thanks Luigi. Using your tip, I was able to resolve the issue by increasing the number of processes.

  55. 55 Hello from Ukraine

    Thanks for
    “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;
    fix too.

    You made me happy :)

  56. 56 Ashish

    I had oracle 10g 10.1.0.2.0, i removed it and installed 11g 11.1.0.6.0standard edition, then i exported all the dmp file in my newly created database, but when i run my application server i get an error ora-12519 on application running client machine and at the same time i get error on server maximum processes(%s%) exceeded, when i try to login in sqlplus. I increased it upto 500 but it still not resolved.

  57. 57 thomas

    thank you very much for this hint. now my code works fine on win2000 and ubuntu.

  58. 58 vincent

    Great post! It fixed my problem :) Just wondering though if there’s a way to revert the processes default value. Do you know how?

  59. 59 Jagannath Shukla

    Thanks, It is usefull article. it resolved my problem.

  60. 60 Dibakar Deb

    Its Realy a great help….

    Thanks

  61. 61 Alex

    Thanks a lot !
    You save my day LOL

    Why Oracle XE is configured with a so default small processes limit !!
    A developper must be able to develop a Batch process for a Production environment on his local machine with an Oracle XE without thinking about this…

    You blog help me to understand some Oracle features LOL

  62. 62 progressor

    i tried it but shows error “insufficient privileges”.

  63. 63 Thorsten

    This trick is still required in 2017. I have to move a lot of data around and this trick relaxed the situation.

    When you are using Java (JDBC/Spring), it may help to wait between using connections with something like

    Thread.sleep(30);

  1. 1 How Oracle Bugged Me .. « Cup of Java , Linux Maar Ke..
  2. 2 Digital Sanctuary Tech » Blog Archive » Why Oracle makes me nuts
  3. 3 Confluence: Customer Solutions
  4. 4 Oracle XE, TNS:no appropriate service handler found « Tangga-tangga waktu….
  5. 5 Fabio Brandão - Blog » Blog Archive » ORA-12519, TNS:no appropriate service handler found
  6. 6 Getting Oracle XE to work with SpringFramework JDBC Template » Pass By Value
  7. 7 Confluence: OTTO Development
  8. 8 Confluence: eTouchPoint
  9. 9 Confluence: eTouchPoint
  10. 10 Confluence: Development
  11. 11 Confluence: Finance Portal
  12. 12 Confluence: Drew 360 Project (Drew resources)


Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



Calendar

June 2007
M T W T F S S
« May   Jul »
 123
45678910
11121314151617
18192021222324
252627282930  

Follow me

twitter flickr LinkedIn feed

Subscribe by email

Enter your email address:

Archives


Categories

Tag Cloud


Listening