oracle10g,又遭遇了04031错误

原创 2004年09月22日 16:43:00

ORA-00604: error occurred at recursive SQL level 5
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")

让我们来看看metalink的解释:

fact: Oracle Server - Enterprise Edition
symptom: ORA-04031: unable to allocate %s bytes of shared memory (/"%
s/",/"%s/",/"%s/",/"%s/")
cause: New object can not be loaded into shared pool. Shared pool is either
too small or too fragmented or both.

10g是号称不需要使用过多内存就可以的了哦
继续来看看:




D:/oracle/product/BIN>sqlplus "/as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on 星期五 9月 24 14:20:06 2004

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
__shared_pool_size                   big integer 32M
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 1677721
shared_pool_size                     big integer 32M
shared_server_sessions               integer
shared_servers                       integer     1
SQL>
SQL> conn /as sysdba
Connected.
SQL> conn scott/tiger
ERROR:
ORA-28001: the password has expired


Changing password for scott
New password:
Retype new password:
Password changed
ERROR:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select count(*) from sys.job...","Typecheck heap","kgghteInit")


Error accessing package DBMS_APPLICATION_INFO
Connected.
出现错误,不知道为什么。我们再次登陆看看:
SQL> conn /as sysdba
Connected.
SQL> conn scott/frank
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")


Error accessing package DBMS_APPLICATION_INFO
Connected.
SQL>



OK,我们以SYSTEM执行下PUPBLD.SQL,再来看看结果:

SQL> conn system/frank
ERROR:
ORA-00604: error occurred at recursive SQL level 4
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket_cn...","Typecheck heap","kgghteInit")


Error accessing package DBMS_APPLICATION_INFO
Connected.
SQL> @?/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 4
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket_cn...","Typecheck heap","kgghteInit")


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select name,password,datats#...","Typecheck heap","kgghteInit")


DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 5
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
                                         *
ERROR at line 1:
ORA-01430: column being added already exists in table


CREATE TABLE SQLPLUS_PRODUCT_PROFILE
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 4
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 5
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket_cn...","Typecheck heap","kgghteInit")


CREATE VIEW PRODUCT_PRIVS AS
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object

 

Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select name,intcol#,segcol#,...","Typecheck heap","kgghteInit")


CREATE PUBLIC SYNONYM PRODUCT_PROFILE FOR SYSTEM.PRODUCT_PRIVS
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object


DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 5
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")


CREATE SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.SQLPLUS_PRODUCT_PROFILE
               *
ERROR at line 1:
ORA-00955: name is already used by an existing object


DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 4
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared
pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")

CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL>

看来这个不是解决问题的办法



其实这个问题是我在试图占用很少内存的时候使用oracle10g遇到的,因为我的本本内存只有256m,:(
SQL> create pfile='d:/ora10g.ora' from spfile;
编辑文件,删除这样一行:orcl.__shared_pool_size=33554432
*.shared_pool_size=53554432,保存

SQL> startup force pfile='d:/ora10g.ora'
ORACLE instance started.

Total System Global Area   83886080 bytes
Fixed Size                   787568 bytes
Variable Size              78642064 bytes
Database Buffers            4194304 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
在连接就正常了
SQL> conn scott/frank
Connected.
可为什么要那么共享内存呢?




郁闷,继续

SQL> create spfile from pfile='d:/ora10g.ora';

File created.

SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 2726297
shared_pool_size                     big integer 52M
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show sga

Total System Global Area   83886080 bytes
Fixed Size                   787568 bytes
Variable Size              78642064 bytes
Database Buffers            4194304 bytes
Redo Buffers                 262144 bytes
SQL> startup force
SQL> alter system set shared_pool_size=30m scope=both;
alter system set shared_pool_size=30m scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size

ORA-00604和ORA-04031导致数据库实例宕机

问题描述 某用户数据库数据库突然宕机,查看日志发现宕机前大量出现如下错误: Errors in file /u01/oracle/admin/orcl/bdump/orcl2_smon_143...
  • wenzhongyan
  • wenzhongyan
  • 2015年11月17日 10:18
  • 1885

ORACLE10G 出现00600的一个BUG的解决方案

今天又一同事遇到一个oracle数据库bug问题,如下: --ORACLE 10.1 OR 10.2中所有平台都存在该问题. 在进行多表关联复杂查询时出现 ORA-00600: 内部错误代码...
  • lichangzai
  • lichangzai
  • 2013年01月25日 16:47
  • 1208

在win7环境下oracle10g安装问题

虽然oracle10g这个版本已经比较老,但是他比较经典。并且许多高校都用这个版本。所以把安装过程中所有问题贴出来。 ORACLE 10g下载地址 现在直接点击不能下载了要经过oracle许可才可以...
  • u012052268
  • u012052268
  • 2015年05月21日 15:45
  • 3723

Dyn DNS遭遇DDOS攻击,作为小白,我该怎么保护自己的电脑

故事背景昨天晚上到今天早晨据说“半个美国互联网”都瘫痪了,就是因为DDoS攻击——Twitter、GitHub、Spotify、Airbnb、Etsy都受到影响。上如:(有种沦陷的感觉) Dyn...
  • cakushin7433
  • cakushin7433
  • 2016年10月23日 14:26
  • 473

Oracle10g完全卸载正确步骤(详细图文教程)

Oracle卸载要求比较严格,不能简单的卸载就完事了;当然Oracle卸载也没有那么难,只是步骤比较多。Oracle10g还是Oracle11g卸载步骤都是一样的。下边详细介绍一下。   ...
  • lanchengxiaoxiao
  • lanchengxiaoxiao
  • 2014年11月19日 14:16
  • 6633

配置Oracle10g即时客户端plsql的配置

看到网上有好多的Oracle客户端精简版本,但是这些都不是出自Oracle官方之手,难免可能会出现一些问题。经过我奋战一个小时终于搞定了这个 Oracle10g即时客户端 的配置了 1、先到Orac...
  • tianyazaiheruan
  • tianyazaiheruan
  • 2013年12月13日 09:31
  • 2900

Oracle10g完全卸载正确步骤

Oracle卸载要求比较严格,不能简单的卸载就完事了;当然Oracle卸载也没有那么难,只是步骤比较多。Oracle10g还是Oracle11g卸载步骤都是一样的。下边详细介绍一下 工具/原料 Ora...
  • u010362354
  • u010362354
  • 2015年03月09日 16:25
  • 6632

oracle 10g 学习之客户端安装和配置(2)

概述 Oracle 数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置) 服务器与客户机是软件概念, 它们与计算机硬件不存在一一对应的关系. 即: 同一台计算机...
  • binyao02123202
  • binyao02123202
  • 2013年11月24日 20:56
  • 1767

ORACLE 10g下载地址

要感谢http://www.blogjava.net/wangdetian168/archive/2011/03/01/345428.html这位仁兄,把用户名和密码奉献,省得大家去注册了,转的时候不...
  • noway
  • noway
  • 2014年07月09日 11:05
  • 4452

Oracle 10g安装图解教程

Oracle是世界上最大的中间件、数据库、编程语言提供商,Oracle 数据库是一种网络上的数据库, 它在网络上支持多用户, 支持服务器/客户机等部署(或配置)   服务器与客户机是软件概念, 它们与...
  • weixin_36380516
  • weixin_36380516
  • 2017年03月25日 12:12
  • 1189
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle10g,又遭遇了04031错误
举报原因:
原因补充:

(最多只允许输入30个字)