How to modify sga and pga in oracle

Method: 1. Use "alter system set sga_max_size/sga_target=numeric value..." to modify the size of sga; 2. Use "alter system set pga_aggregate_target=numeric value..." to modify the size of pga.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to modify sga and pga in oracle

1. Modify the sga size:

SQL> alter system set sga_max_size=2560M scope=spfile;
System altered.
SQL> alter system set sga_target=2560M scope=spfile;
System altered.

2. Modify the pga size:

SQL> alter system set pga_aggregate_target=650M scope=spfile;
System altered.

Expand knowledge:

Modify the size of instance sga and pga:

Idea: First create a pfile as a backup by specifying the path in spfile, and then adjust the memory parameters in the database.

—>Modify sga, pga related parameters in the database—>Create spfile through this pfile—>Close the database—>Finally start the database normally

First: Create a spfile backup

#/u01/app/oracle/admin/orcl/pfile/pfile1.ora是通过当前spfile创建的文件名字叫 “pfile1.ora”
SQL> create pfile='/u01/app/oracle/admin/orcl/pfile/pfile1.ora' from spfile;
File created.
SQL>

1. Check the memory_target and memory_max_target sizes:

SQL> show parameter memory
NAME     TYPE VALUE
-------------------------------- ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer  0
memory_target     big integer  0
shared_memory_address     integer 0
SQL>

#According to the output results, memory_max_target=0 and memory_target=0 are displayed, indicating that it is already in ASMM mode. You can directly modify the sga and pga sizes

2. (If the value is non-zero) Modify the size of memory_target and memory_max_target:

#If memory_max_target is non-0 and memory_target is non-0, use the following command to change the value to 0

1 , Modify memory_max_target size:

SQL> alter system set memory_max_target=0 scope=spfile;
System altered.

2. Modify memory_target size (memory_target<=memory_max_target):

SQL> alter system set memory_target=0 scope=spfile;
System altered.

The above is the detailed content of How to modify sga and pga in oracle. For more information, please follow other related articles on the PHP Chinese website!