Helpful Oracle hints I've found and saved

How to Increase Oracle SGA

In my post about "Increasing the size of the SGA in Oracle", I gave all the info I found on how to determine what size the SGA should be.

BUT, I never actually gave the commands I used to increase the size.

You can obviously do this easily in Enterprise Manager, and if you need the screenshots on how to do that, let me know, but I just like how quick SQL commands are via SQL*Plus.

So, here they are:

  1. Shutdown the database

    shutdown immediate;

  2. Start up the database in "nomount" – I like to do a full shutdown first because I'm a Windows user and full reboots are better than restarts 🙂

    startup nomount;

  3. Run the following sql to increase the SGA_MAX_SIZE parameter to 4GB. You MIGHT not need to do this, but just remember, you won't be able to increase the SGA_TARGET parameter to a value higher than the MAX SIZE parameter

    alter system set sga_max_size = 4000M scope=SPFILE;

  4. Run the following sql to increase the SGA_TARGET parameter

    alter system set sga_target = 4000M scope=SPFILE;

  5. Shutdown the database – see point #2 about why I'm wasting time doing full restarts.

    shutdown immediate;

  6. Startup the database.


Of course, you'll have to make sure your system has that amount of memory available to allocate to the SGA.

No Responses to “How to Increase Oracle SGA”


  1. Increase the size of the SGA | Oracle Tips - [...] How to increase Oracle’s SGA Size Parameters [...]

Leave a Reply

Powered by WordPress | Designed by Elegant Themes

Pin It on Pinterest

Share This