oracle lock_sga ORA-00847

最新推荐文章于 2021-04-05 22:53:33 发布

原创最新推荐文章于 2021-04-05 22:53:33 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE 之内存管理与优化专栏收录该内容

23 篇文章

订阅专栏

本文介绍了Oracle数据库中lock_sga参数的作用与配置方法。lock_sga可以将SGA锁定在物理内存中，避免使用虚拟内存导致性能下降。文章通过实例展示了如何设置此参数，并讨论了与MEMORY_TARGET等参数的兼容性问题。

oracle lock_sga

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.

eg:

oracle:~ # su - oracle
oracle@oracle:~> sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 6月 27 21:39:14 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到: 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl#show parameter sga_target

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_target                           big integer
512M
SYS@orcl#set linesize
SP2-0267: linesize 选项 0 超出范围 (自 1 至 32767)
SYS@orcl#
SYS@orcl#
SYS@orcl#
SYS@orcl#set linesize 200
SYS@orcl#show parameter sga_target

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
sga_target                           big integer            512M
SYS@orcl#show parameter sga_max_target
SYS@orcl#show parameter sga_max

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
sga_max_size                         big integer            772M
SYS@orcl#show parameter lock_sga

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga                             boolean                FALSE
SYS@orcl#create pfile='./pfile.ora' from spfile;

文件已创建。

SYS@orcl#!free -m                 
             total       used       free     shared    buffers     cached
Mem:          1004        973         31          0         35        683
-/+ buffers/cache:        254        750
Swap:         2055        108       1946

SYS@orcl#alter system set lock_sga=true scope=spfile;

系统已更改。

SYS@orcl#start force
SP2-0310: 无法打开文件 "force.sql"
SYS@orcl#startup force
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set together
SYS@orcl#

解决办法：见 ID 577898.1
参考文章： http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1176205889966
http://www.askmaclean.com/archives/know-more-about-lock_sga-parameter.html
http://managingoracle.blogspot.com/2010/06/locksga-and-asmm.html