oracle 获取 java.sql.SQLException: ORA-04031:
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2084791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting java.sql.SQLException: ORA-04031:
提问by Vicky
I am getting Following when query executing on through web application
我在通过 Web 应用程序执行查询时得到关注
java.sql.SQLException: ORA-04031:
java.sql.SQLException: ORA-04031: unable to allocate 48784 bytes of shared memory ("shared pool","SELECT emplid levempid, '2...","Typecheck","qry_text : qcpisqt")
But same query is executing through TOAD properly.
但是相同的查询正在通过 TOAD 正确执行。
回答by Rob van Wijk
Chances are very high that your Java code is not making use of bind variables. In that case each SQL statement is unique and will not be reused, thrashing the shared pool. It will become fragmented and finally results in the ORA-04031.
您的 Java 代码没有使用绑定变量的可能性非常高。在这种情况下，每个 SQL 语句都是唯一的，不会被重用，从而破坏共享池。它将变得支离破碎并最终导致 ORA-04031。
Restarting the database will only temporarily work, but eventually you will run into the same problems. Increasing the shared pool size and regularly restarting the database is not a real solution. Only real solution is to rewrite your SQL to use bind variables.
重新启动数据库只会暂时起作用，但最终您会遇到同样的问题。增加共享池大小并定期重新启动数据库并不是真正的解决方案。唯一真正的解决方案是重写您的 SQL 以使用绑定变量。
Here is an AskTom thread of someone experiencing something similar: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:528893984337
这是某人遇到类似问题的 AskTom 线程：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:528893984337
And a nice little program to track which SQL statements are not making use of bind variables can be found in this thread: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1163635055580
在这个线程中可以找到一个很好的小程序来跟踪哪些 SQL 语句没有使用绑定变量：http://asktom.oracle.com/pls/apex/f?p=100:11:0::: :P11_QUESTION_ID:1163635055580
Hope this helps.
希望这可以帮助。
Regards, Rob.
问候，罗伯。
回答by pugmarx
Found a similar question on oracle forum, and the solution that seemed to have work is:
在oracle 论坛上发现了一个类似的问题，似乎有效的解决方案是：
The user didn't have
server=dedicatedin their tnsnames entry. Adding this line in made the query work.
用户
server=dedicated在他们的 tnsnames 条目中没有。添加此行使查询工作。
Hope this helps.
希望这可以帮助。
回答by ratty
In metalink,they state this as :
在 metalink 中，他们将其声明为：
The ORA-04031 error is usually due to fragmentation in the library cache or shared pool reserved space. Before of increasing the shared pool size consider to tune the application to use shared sql and tune SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, and SHARED_POOL_RESERVED_MIN_ALLOC. First determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by issuing the following query:
ORA-04031 错误通常是由于库缓存或共享池保留空间中的碎片造成的。在增加共享池大小之前，请考虑调整应用程序以使用共享 sql 并调整 SHARED_POOL_SIZE、SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_RESERVED_MIN_ALLOC。首先通过发出以下查询来确定 ORA-04031 是否是库缓存或共享池保留空间中的碎片的结果：
SELECT free_space, avg_free_size, used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved; The ORA-04031 is a result of lack of contiguous space in the shared pool reserved space if: REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.
从 v$shared_pool_reserved 中选择 free_space、avg_free_size、used_space、avg_used_size、request_failures、last_failure_size；ORA-04031 是共享池保留空间中缺少连续空间的结果，如果：REQUEST_FAILURES 是 > 0 并且 LAST_FAILURE_SIZE 是 > SHARED_POOL_RESERVED_MIN_ALLOC。
To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.
要解决此问题，请考虑增加 SHARED_POOL_RESERVED_MIN_ALLOC 以减少缓存到共享池保留空间中的对象数量，并增加 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 以增加共享池保留空间中的可用内存。
The ORA-04031 is a result of lack of contiguous space in the library cache if:
ORA-04031 是库缓存中缺少连续空间的结果，如果：
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
REQUEST_FAILURES 是 > 0 并且 LAST_FAILURE_SIZE 是 < SHARED_POOL_RESERVED_MIN_ALLOC
or
或者
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
REQUEST_FAILURES 为 0，LAST_FAILURE_SIZE 为 < SHARED_POOL_RESERVED_MIN_ALLOC
The first step would be to consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to put more objects into the shared pool reserved space and increase SHARED_POOL_SIZE......
第一步是考虑降低 SHARED_POOL_RESERVED_MIN_ALLOC 以将更多对象放入共享池保留空间并增加 SHARED_POOL_SIZE......
