Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
How to reduce SGA allocated at start of instance?

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.
Best 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
-
No other way. You need to bounce the instance for values to take effect.
-
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
-
SQL> -- TRY TO CHANGE THE SGA_MAX_SIZE WHICH NOT ALLOW BECAUSE SGA_MAX_SIZE IS STATIC PARAMETER
SQL> alter system set sga_max_size=2g scope=both;
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
You can take approval of restart the database from client.
-
Thank you all for the inputs... I also thought so and confirmed with all your experts answers.