#修改实例SGA与PGA大小（ASMM）
[oracle@oracle:/oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 6 23:23:47 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@oracle:/oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 6 23:24:05 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 884999048 bytes
Database Buffers 176160768 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
#在对数据库参数修改前创建pfile文件作为spfile备份
SP2-0734: unknown command beginning "在对数据库..." - rest of line ignored.
SQL> create pfile='/tmp/pfile_bak.ora' from spfile;
File created.
#查看memory_target与memory_max_target大小：
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
#根据输出的结果显示 memory_max_target=0 与 memory_target=0 说明目前已经是 ASMM 模式直接修改sga与pga大小即可
#(如果值非零) 修改memory_target与memory_max_target大小：
#如果memory_max_target非0 与 memory_target非0，使用下面命令将值改为0
# 修改memory_max_target大小;
SQL> alter system set memory_max_target=0 scope=spfile;
System altered.
#修改memory_target大小（memory_target<=memory_max_target）：
SQL> alter system set memory_target=0 scope=spfile;
System altered.
#修改SGA与PGA大小
修改sga大小：
SQL> alter system set sga_max_size=960M scope=spfile;
System altered.
SQL> alter system set sga_target=960M scope=spfile;
System altered.
修改pga大小：
SQL> alter system set pga_aggregate_target=512M scope=spfile;
System altered.
#因为将参数修改写在了spfile中，在数据库重启后参数生效
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1002127360 bytes
Fixed Size 2259440 bytes
Variable Size 884999696 bytes
Database Buffers 109051904 bytes
Redo Buffers 5816320 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 960M
sga_target big integer 960M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 960M
sga_target big integer 960M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 512M
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
