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.
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!