ORACLE_修改实例的内存大小

本是古典何须时尚 2021-12-20 17:04 234阅读 0赞

注：本文来源于：[星火spark][spark] 《[Oracle的实例占用内存调整][Oracle]》

## ORACLE\_修改实例的内存大小 ##

### 一：修改oracle数据库实例内存大小脚本 ###

1  ---- 1、操作 （oracle使用内存约等于 SGA+PGA，所以可以减少SGA与PGA解决你的问题，生产库慎用）
      2 alter system set sga_max_size=100m scope=spfile; --减少SGA大小，静态参数，重启后生效
      3 alter system set sga_target=80m scope=both; --动态参数；oracle推荐：启动时修改此参数，不要动态设置此参数
      4 alter system set pga_aggregate_target=40m sope=both; ---减少pga大小

### 二：知识点拓展： ###

SGA\_MAX\_SIZE的大小不是随意指定的，必须满足一定条件的。  
sga\_max\_size=100M,必须满足SGA所有组件的最小和；至少满足db\_cache\_size,log\_buffer,shared\_pool\_size,large\_pool\_size,java\_pool\_size总和的大小)

1 oracle内存结构介绍：
      2 Introduction to Oracle Memory Structures
      3 Oracle uses memory to store information such as the following:
      4 
      5 Program code
      6 Information about a connected session, even if it is not currently active
      7 Information needed during program execution (for example, the current state of a query from which rows are being fetched)
      8 Information that is shared and communicated among Oracle processes (for example, locking information)
      9 Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)
     10 The basic memory structures associated with Oracle include:
     11 System Global Area (SGA), which is shared by all server and background processes.
     12 Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process.
     13 
     14 1.sqlplus “/ as sysdba"  2. 3.create pfile='d:/cj.ora' from spfile;  然后去你的oracle安装目录
     15 　　找一个init数据库名。ora这样一个文件
     16 
     17 　　把几个oracle内存参数改小点
     18 
     19 　　一个2G的内存是如下配置的：
     20 
     21 　　*.sga_max_size=712M
     22 
     23 　　*.large_pool_size=8M
     24 
     25 　　*.shared_pool_size=250M
     26 
     27 　　*.sort_area_size=0.5M
     28 
     29 　　*.db_cache_size=350M
     30 
     31 　　然后shutdown数据库
     32 
     33 　　1.shutdown immediate;  2. 3.create spfile from pfile='d:/cj2.ora';  4. 5.startup;  然后启动，就可以了。
     34 
     35 　　以上就是调整oracle内存参数，减少数据库内存占用的方法介绍。

转载于:https://www.cnblogs.com/ios9/p/7965407.html

[spark]: http://www.cnblogs.com/sparkbj/
[Oracle]: http://www.cnblogs.com/sparkbj/p/6146363.html