Changing Oracle Memory Settings

Posted on 2012-08-16
Medium Priority
Last Modified: 2013-08-07
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.
Question by:cto2008
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1000 total points
ID: 38304541
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;

Pick whater memory size you want Oracle to have access to.

Author Comment

ID: 38322282
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,

Assisted Solution

miyahira earned 1000 total points
ID: 38410224
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%).

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).
LVL 23

Expert Comment

by:Steve Wales
ID: 39388557
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question