Adjusting SGA & PGA In Oracle Databases
1. Check the Current Configuration Before making any changes, verify if memory_target is set: SHOW PARAMETER memory_target; If memory_target is not 0, follow these steps: 2. Check Current SGA and PGA Values Run the following commands to view the current configurations: SHOW PARAMETER sga_target; SHOW PARAMETER sga_max_size; SHOW PARAMETER pga_aggregate_target; 3. Calculate and Set New Targets To adjust the SGA and PGA sizes, use the following commands. Replace the values with your desired configuration if needed: ALTER SYSTEM SET SGA_TARGET = 32G SCOPE = SPFILE; ALTER SYSTEM SET SGA_MAX_SIZE = 32G SCOPE = SPFILE; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 24G SCOPE = SPFILE; Note: If Automatic Memory Management (AMM) is enabled ( memory_target is not 0), adjust only MEMORY_TARGET instead of SGA_TARGET and PGA_AGGREGATE_TARGET . 4. Restart the Database To apply the changes, restart the database: SHUTDOWN IMMEDIATE; STARTUP; 5. Verify the New Configurati...