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.

Recommended tutorial: "Oracle Video Tutorial"

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!