How to modify sga in oracle

Method: 1. Execute the "alter system set sga_max_size=numeric scope=spfile" command; 2. Execute the "alter system set sga_target=numeric scope=spfile" command; 3. Restart the database.

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

How oracle modifies sga

Example: Modify SGA from 2G to 32G

Method 1. Modify through spfile

1 ) Use the SYS user to log in to the system as SYSDBA

$ sqlplus / as sysdba

2) Query the memory_target parameter

3) Execute the command to modify the following parameters

SQL>alter system set sga_max_size=32G scope=spfile;
SQL>alter system set sga_target=32G scope=spfile;

Note: alter system set sga_target=1600m scope=spfile; must be followed by scope=spfile to specify the modification of Spfile. Otherwise, the error "Cannot modify initialization parameters" will be reported.

4) Restart the database to make the modification take effect

SQL> shutdown immediate;
SQL> startup ;

Method 2. Modify through pfile parameters

1) Create pfile

SQL>create pfile from spfile;

The default storage path is: $ORACLE_HOME/dbs

2) Modify the pfile content

$ vi INITorcl.ORA  //假设 1）中生成的 pfle 文件名为INITorcl.ORA

The main content after modification is

sga_target=34359738368(32G)
lock_sga=true
pga_aggregate_tagert=250000000(250M左右)
workarea_size_policy=auto
pre_page_sga=true
sga_max_size=34359738368(32G)

3) Use pfile to start the database

SQL>startup pfile='$ORACLE_HOME/dbs/INITorcl.ORA'

Recommended tutorial: "Oracle Video Tutorial"

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