Thursday, 9 June 2016

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 comments:

Post a Comment