评论区大佬说的对……运行以下命令重启服务
alter system set memory_max_target=XXXm scope=spfile;
alter system set memory_target=XXXm scope=spfile;
sga_max_size
不能比 memory_target
下面的不用看了,当作是改配置文件的方法了
没想到还有人看到这个文章
说明
这个内存大小应该是我们在安装Oracle数据库时指定的,下面记录使用命令更改Oracle内存大小的方法
查看内存的命令
需在命令窗口或sqlplus中执行~
show parameter sga;
例:
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2464M
sga_target big integer 0
更改内存大小的SQL命令
alter system set sga_max_size=10240m scope=spfile;
这里 sga_max_size
为指定的内存大小
例子:
SQL> alter system set sga_max_size=10240m scope=spfile;
System altered
使配置生效
上面改了之后,查询,还没生效
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2464M
sga_target big integer 0
我这里直接 重启数据库
发现这个错误
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 10737418240 cannot be set to more than MEMORY_TARGET 258
3691264.
解决方法如下
SQL> create pfile='E:\init0220.ora' from spfile;
文件已创建。
SQL>
这里将 配置文件导出到 E盘下
到E盘,用编辑工具打开这个文件
我的是这样的内容
orcl.__db_cache_size=855638016
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base=‘C:\app\Administrator’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=889192448
orcl.__sga_target=1694498816
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=754974720
orcl.__streams_pool_size=16777216
*.audit_file_dest=‘C:\app\Administrator\admin\orcl\adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=‘C:\app\Administrator\oradata\orcl\control01.ctl’,‘C:\app\Administrator\flash_recovery_area\orcl\control02.ctl’
*.db_block_size=8192
*.db_domain=‘’
*.db_name=‘orcl’
*.db_recovery_file_dest=‘C:\app\Administrator\flash_recovery_area’
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest=‘C:\app\Administrator’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.local_listener=‘LISTENER_ORCL’
*.memory_target=2577399808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_max_size=10737418240
*.undo_tablespace=‘UNDOTBS1’
注:勿复制我的这个文件内容
这里呢,需要改的地方为
*.memory_target=1073741824
*.sga_max_size=805306368
*.sga_target=805306368
因为我配置的 sga_max_size
比 memory_target
大,所以就报了上面的错,下面将这几个都调大一些各行了(我都给它们后面加了个0,额~)
orcl.__db_cache_size=855638016
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base=‘C:\app\Administrator’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=889192448
orcl.__sga_target=8053063680
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=754974720
orcl.__streams_pool_size=16777216
*.audit_file_dest=‘C:\app\Administrator\admin\orcl\adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=‘C:\app\Administrator\oradata\orcl\control01.ctl’,‘C:\app\Administrator\flash_recovery_area\orcl\control02.ctl’
*.db_block_size=8192
*.db_domain=‘’
*.db_name=‘orcl’
*.db_recovery_file_dest=‘C:\app\Administrator\flash_recovery_area’
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest=‘C:\app\Administrator’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.local_listener=‘LISTENER_ORCL’
*.memory_target=10737418240
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_max_size=8053063680
*.undo_tablespace=‘UNDOTBS1’
作完修改后,记得保存
然后在启动数据时指定 配置文件即可
SQL> startup pfile='E:\init0220.ora'
ORACLE 例程已经启动。
Total System Global Area 8017100800 bytes
Fixed Size 2181944 bytes
Variable Size 2483029192 bytes
Database Buffers 5502926848 bytes
Redo Buffers 28962816 bytes
数据库装载完毕。
数据库已经打开。
OK,检查一下
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 7680M
sga_target big integer 0