oracle10g,又遭遇了04031错误

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