oracle内存管理手动到自动

数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

确定内存为手动管理

SQL> show parameter target;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target           integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target             integer     0
fast_start_mttr_target           integer     0
memory_max_target            big integer 0
memory_target                big integer 0
parallel_servers_target          integer     512
pga_aggregate_target             big integer 35225M
sga_target               big integer 4G

修改为自动管理

SQL> alter system set sga_target=0 scope=spfile;

System altered.

SQL> alter system set pga_aggregate_target=0 scope=spfile;

System altered.

SQL> alter system set memory_max_target=38G scope=spfile;

System altered.

SQL> alter system set memory_target=38G scope=spfile;

System altered.

重启数据库使其生效

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4.0620E+10 bytes
Fixed Size          2262008 bytes
Variable Size        4.0400E+10 bytes
Database Buffers      134217728 bytes
Redo Buffers           84008960 bytes
Database mounted.
Database opened.

确定参数修改成功

SQL> show parameter target;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target           integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target             integer     0
fast_start_mttr_target           integer     0
memory_max_target            big integer 38G
memory_target                big integer 38G
parallel_servers_target          integer     512
pga_aggregate_target             big integer 0
sga_target               big integer 0

注：修改的oracle内存不要大于物理内存，这点要检查好单位是GB/MB/KB，否则会出现startup没有任何反应，详见我的blog。
http://blog.csdn.net/dbdoctor/article/details/51852005