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.

    startup;

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”

Trackbacks/Pingbacks

  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

Shares
Share This