ORA-12519, TNS:no appropriate service handler found

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! I’ll try to install it on my cellphone) 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!