Change sessions, transaction and processes parameters in Oracle 10g

alter system set PROCESSES=100 scope=SPFILE;
alter system set TRANSACTIONS=126 scope=SPFILE;
alter system set SESSIONS=115 scope=SPFILE;

Tuning Oracle XE

CONNECT SYS/CONTRASEÑA@XE AS SYSDBA;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;


alter system set aq_tm_processes = 1 scope=both;

alter system set db_cache_size = 150994944 scope=both;
alter system set db_file_multiblock_read_count = 32 scope=both;
alter system set db_recovery_file_dest_size = 2147483648 scope=both;
alter system set java_pool_size = 125829120 scope=both;
alter system set large_pool_size = 4194304 scope=both;
alter system set max_commit_propagation_delay = 1 scope=both;
alter system set open_cursors = 400 scope=both;
alter system set pga_aggregate_target = 203423744 scope=both;
alter system set processes = 300 scope=spfile;
alter system set session_max_open_files = 50 scope=spfile;
alter system set sessions = 400 scope=spfile;
alter system set sga_max_size = 629145600 scope=spfile;
shutdown;

alter system set sga_target = 629145600 scope=spfile;
alter system set shared_pool_size = 184549376 scope=both;
alter system set star_transformation_enabled = TRUE scope=both;
alter system set max_commit_propagation_delay=700 scope=both;
alter system set max_dispatchers=5 scope=both;
alter system set max_shared_servers=20 scope=both;
alter system set max_enabled_roles=150 scope=both;
alter system set shared_server_sessions=165 scope=both;
alter system set sort_area_size=524288 scope=both;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

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!