ORA-00604: Error Occurred At Recursive Sql Level 5ora-04031: Unable to Allocate 4096 BYtes of Shared Memory ("Shared Pool", "SELECT / * Rule * / BUCKET, E ...", "TypeCheck Heap", " Kgghteinit ")
Let's take a look at Metalink's explanation: Fact: Oracle Server - Enterprise EditionSymptom: 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 each it. SMALL OR TOO FRAGMENTED or Both.10G is the name. You don't need to use too much memory. Oh, continue to see Look:
D: / Oracle / Product / Bin> SQLPlus "/ as sysdba"
SQL * Plus: Release 10.1.0.2.0 - Production on Friday September 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 - Productionwith The Partitioning, OLAP AND DATA MINING OPTIONS
SQL> show parameter shared
Name Type Value --------------------------------- ------------------------------__ shared_pool_size big integer 32Mhi_shared_memory_address integer 0max_shared_servers integershared_memory_address integer 0shared_pool_reserved_size big integer 1677721shared_pool_size big integer 32Mshared_server_sessions integershared_servers integer 1SQL> SQL > conn / as sysdbaconnected.sql> conn scott / tigererror: ora-28001: The password Has Expired
Changing password for scottNew password: Retype new password: Password changedERROR: ORA-00604: error occurred at recursive SQL level 3ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "select count (*) from sys.job ... "" Once again, we look at the landing: SQL> conn / as sysdbaConnected.SQL> conn scott / frankError accessing PRODUCT_USER_PROFILEWarning:! Product user profile information not loaded You may need to run PUPBLD.SQL as SYSTEMERROR: ORA-00604: error occurred at recursive SQL level 3ORA-04031: Unable to allocate 4096 bytes of shared memory ("SharedPool", "SELECT / * Rule * / Bucket, E ...", "Typecheck HEAP", "KGGHTeinit")
Error Accessing Package DBMS_Application_infoconnected.sql>
OK, let's perform Puppbld.sql under system, then look at the results:
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_INFOConnected.SQL> @ / sqlplus / admin / pupbld.sqlDROP SYNONYM PRODUCT_USER_PROFILE * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 4ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool "," 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 3ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "select name, password, datats # ...", " Typecheck heap "," kgghteInit ") DROP TABLE PRODUCT_USER_PROFILE * eRROR at line 1: ORA-00604: error occurred at recursive SQL level 5ORA-04031: unable to allocate 4096 bytes of shared memory (" sharedpool "," 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 4ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "select / * rule * / bucket, e ... "," TypeCheck Heap "," KGGHTeinit ")
DROP VIEW PRODUCT_PRIVS * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 5ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "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 2ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "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 objecting Object
DROP SYNONYM PRODUCT_USER_PROFILE * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 5ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "select / * rule * / bucket, e ... "," TypeCheck Heap "," KGGHTeinit ")
Create synynym product_user_profile for system.sqlplus_product_profile * error at line 1: ora-00955: Name is already used by an existing objectY b
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 4ORA-04031: unable to allocate 4096 bytes of shared memory ( "sharedpool", "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>
It seems that this is not a solution to the problem.
In fact, this problem is that I am experiencing the Oracle10G when I try to take us less memory, because my book is only 256m, :( SQL> CREATE PFILE = 'D: /ora10g.ora' from spfile; editing file, delete this One line: orcl .__ shared_pool_size = 33554432 * .shared_pool_size = 53554432, saving SQL> Startup force Pfile = 'd: /ora10g.ora'oracle instance start.
Total System Global Area 83886080 bytesFixed Size 787568 bytesVariable Size 78642064 bytesDatabase Buffers 4194304 bytesRedo Buffers 262144 bytesDatabase mounted.Database opened. In connection to normal SQL> conn scott / frankConnected. But why then shared memory? Depressed, continue
SQL> CREATE SPFILE from Pfile = 'D: /ora10g.ora'
File created.
SQL> show parameter shared
Name Type Value --------------------------------- ------------------------------ hi_shared_memory_address integer 0max_shared_servers integershared_memory_address integer 0shared_pool_reserved_size big integer 2726297shared_pool_size big integer 52Mshared_server_sessions integershared_servers integer 0SQL> show sga
Total System Global Area 83886080 bytesFixed Size 787568 bytesVariable Size 78642064 bytesDatabase Buffers 4194304 bytesRedo Buffers 262144 bytesSQL> startup forceSQL> 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 Invalidora-04034: Unable to Shrink Pool To Specified Size
