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.
