Changing Oracle Memory Settings

Posted on 2012-08-16
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 76

    Accepted Solution

    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

    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,
    LVL 1

    Assisted Solution

    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 22

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
    This tutorial will show how to inventory, catalog, and restore media from legacy versions of Backup Exec into both 2012 and 2014 versions of the software. Select Storage from the tabs along the ribbon bar as the top: Ensure the proper storage devi…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now