Forum Stats

  • 3,827,547 Users
  • 2,260,791 Discussions
  • 7,897,297 Comments

Discussions

How to reduce SGA allocated at start of instance?

Mike301
Mike301 Member Posts: 180 Blue Ribbon
edited Mar 4, 2014 12:22PM in General Database Discussions

Friends...

I was trying to see if there is anyway I can reduce memory allocated to SGA when the db was started, without restarting database?

I'm using "memory_target=4G" parameter in init.ora file, there is no memory_max_target, sga_target or sga_max_size parameter.

DB: 11gR2

OS: Linux

pfile: inti.ora

Scenario:

1. So with the current pfile db was started and SGA was allocated 4G because of "memory_target" parameter.

2. Show parameter displays, SGA_MAX_SIZE = 4G

3. Show SGA; , displays 4G being allocated

4. I did "ALTER SYSTEM SET MEMORY TARGET=2G;"

5. SHOW PARAMETER displays

         MEMORY_TARGET = 2G

         SGA_MAX_SIZE=4G

6. Show SGA;, still displays 4G and memory not reduced since SGA_MAX_SIZE was set as 4G because of MEMORY_TARGET parameter when instance was started.

Oracle manual:

"

On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.\

"

I don't think there is anyways but still wondering, is there anyway to bring SGA to 2G without db restart?

DB is using too much memory and want to reduce that.

Thanks in advance for all inputs.

Tagged:

Best Answer

  • Suntrupth
    Suntrupth Member Posts: 482
    Answer ✓

    Well, SGA_TARGET is the only parameter you can change without a restart , but it doesn't make sense as your SGA allocated will still be the same, Since it is allocated at startup.

    Restart is your only option.

    Regards,

    Suntrupth

Answers

This discussion has been closed.