Configuring Oracle 10g XE for Development

by Christoph Menge in Software

When I first installed Oracle XE, I was quite impressed how much of the maximum amount of 1GB RAM it uses per default: In my case, about 880MB. I din’t really care, because my machine has 3GB RAM, and my XP with some old software did not use up too much…

However, having switched to Windows Vista and brand-new versions of all the tools one needs, I have actually hit the limit of my machine - a small window popped up, suggesting to kill both instances of Visual Studio so I would regain free RAM… For a developer system, it is obviously a good idea to trim the database system’s size rather than not using the IDE…

So, even though most people would like their XE Oracles to consume more RAM:

How to make Oracle’s memory footprint smaller:
1. Start Oracle with its default configuration. You can type net start OracleServiceXE on the (administrator-) console.
2. Find ‘C:\YourOracleDirectory\app\oracle\product\10.2.0\server\config\scripts\init.ora’
Open it and search for sga_target=768M. Replace this with some reasonable value for your machine. You cannot, however, set this value arbitrarily small or Oracle will not work. I used 140M.
Also search for pga_aggregate_target=140M and set it to, say, 40M.
Save the file.
3. Log into SQLplus. In command window, type

sqlplus system/systempass
SQL> CONNECT / AS SYSDBA
Connected.
SQL> create spfile=’C:\YourOracleDirectory\app\oracle\product\10.2.0\server\dbs/spfilexe_small.ora’
FROM pfile=’C:\YourOracleDirectory\app\oracle\product\10.2.0\server\config\scripts\init.ora’
File created.
SQL>>exit

4. Stop Oracle: C:\> net stop OracleServiceXE
5. Back up ‘C:\YourOracleDirectory\app\oracle\product\10.2.0\server\dbs/spfilexe.ora’ and replace it with our newly-created spfile.
6. Start Oracle again: C:\> net start OracleServiceXE

Now, oracle should consume merely about 180MB RAM.

You can also edit the spfile with a hex editor if you are brave enough… But you have to work very carefully then.

Post to Twitter Post to Delicious Post to Digg Post to Facebook

No related posts.

Tags: , , , , ,

← Previous

Next →

Leave a Comment