• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

Changing Oracle Memory Settings

I have Oracle 11g R2 installed on a Windows Server 2008 R2 that is taking every bit of available memory.  This server is meant to have Oracle installed as well as the application that will be using Oracle.  I would like to know how to change the memory settings without having to use the db console as I cannot connect to it. I think it is because there isn't any available memory.
0
cto2008
Asked:
cto2008
2 Solutions
 
slightwv (䄆 Netminder) Commented:
You can change parameters with sqlplus and the ALTER SYSTEM command.

from the database server command prompt do the following and post the results of the show comands:
sqlplus /nolog
SQL> conn / as sysdba
SQL> show parameter memory
SQL> show parameter pga
SQL> show parameter pool
SQL> show parameter sga

Hopefully you only have memory_target and memory_max_target set.  If sga_max_size is set it should be less than the memory settings.

You can reduce the memory values:
alter system set memory_max_target=100M scope=spfile;
alter system set memory_target=100M scope=spfile;
shutdown;
startup;

Pick whater memory size you want Oracle to have access to.
0
 
cto2008Author Commented:
Let me start out by saying that our server has 8gb of memory.  For some reason the database will take up over 7 gigs after it is in use.  On a fresh start it shows about 3.5gbs which is what I have the max set to.  I discovered this using the commands in the post above.     I am wondering if the extra memory may be due to the number of connections.  Does the max include the memory taken by connections?  Do you happen to know how much memory is taken by each connection if this is not the case?

   Thank you,
              Brandon
0
 
miyahiraCommented:
It seems that Oracle and Windows Server 2008 (specially 64-bits) don't get well with memory, but nobody wants to talk about that.

All what is saying is that you should create your database with less memory size than by default (40%).
See:
https://forums.oracle.com/forums/thread.jspa?threadID=1981490&start=30&tstart=0
and
http://stackoverflow.com/questions/5406129/oracle-11g-memory-leak-in-win-2008
and
http://serverfault.com/questions/263082/why-oracle-11g-on-windows-default-installation-keeps-increasing-memory-usage-eve

One question: Why wasn't there any memory trouble in Windows Server 2003?

I've got same memory problem with Oracle RAC 11g and Windows Server 2008 R2 64-bits. Even when idle, server consumes 74% of RAM and it's very slow.

What I'm trying now is a fresh install of Oracle 11g in Windows Server 2008, but creating database with 20% of memory size (not 40% by default).
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now