SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 2G
memory_target big integer 512M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 2G
sga_target big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> ALTER SYSTEM SET memory_target='0' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET memory_max_target='0' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET sga_max_size = 786432000 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET sga_target = 524288000 SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET pga_aggregate_target=100288000 SCOPE=BOTH;
System altered.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 2G
memory_target big integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 2G
sga_target big integer 512M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100288000
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 788529152 cannot be set to more than MEMORY_MAX_TARGET 0.
--如果不小心設了memory_max_target and memory_target，如何完整關閉 Automatic Memory Management
--僅用 ASMM（Automatic Shared Memory Management）
SQL> ALTER SYSTEM SET memory_target='0' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET memory_max_target='0' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET sga_max_size = 786432000 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET sga_target = 524288000 SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET pga_aggregate_target=100288000 SCOPE=BOTH;
System altered.
SQL> create pfile='/tmp/my_pfile.ora' from spfile;
File created.
vi /tmp/my_pfile.ora
找到
*.memory_max_target
*.memory_target
刪除之
CREATE SPFILE = '/tmp/my_spfile.ora' FROM PFILE = '/tmp/my_pfile.ora';
shutdown immediate
cd $ORACLE_HOME/dbs
mv spfileSID.ora spfileSID.ora.bck
cp -p /tmp/my_spfile.ora spfileSID.ora
startup
SQL> startup
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 520097336 bytes
Database Buffers 255852544 bytes
Redo Buffers 6791168 bytes
Database mounted.
Database opened.
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 0
memory_target big integer 0
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 752M
sga_target big integer 512M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100288000
