Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to solve the ORA-04031 error of Oracle". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The cause of the ORA-04031 error:

1. A lot of hard parsing appears.

two。 Suddenly a big SQL appeared.

Solution to 04031 error:

(1) execute SQL > alter system flush shared_pool directly

This method can be alleviated, but there is no fundamental solution to the problem.

(2) convert an unshared SQL into a shared SQL. (use bind variables)

(3) modify cursor_sharing parameters

SQL > alter system set cursor_sharing='force'

After modifying this parameter, you can force binding variables to implement a shared SQL. However, if the SQL cannot be shared due to irregular writing, such as spaces, carriage returns, etc., it cannot be solved at this time.

(4) ① execution @? / rdbms/admin/dbmspool.sql

② SQL > select * from v$db_object_cache where sharable_mem > 10000 and (type = 'PACKAGE' or type =' PACKAGE BODY' or type = 'FUNCTION' or type =' PROCEDURE') and kept = 'NO'

③ executes dbms_shared_pool.keep ('object name')

(5) increase shared_pool space.

SQL > select component,current_size/1024/1024 M from v$sga_dynamic_components

SQL > show parameter sga_target

SQL > show parameter sga_max_size

SQL > alter system set shared_pool_size=150M scope=both

Note: sga_max_size is used to limit the size of sga_targat. Sga_target can never exceed sga_max_size.

(6) reserved area

The reservation is dedicated to caching large SQL.

SQL > select request_misses from v$shared_pool_reserved

This query is the number of times the query request failed in the reserved area. This value is preferably, when this value exists, there is bound to be a 04031 error.

Resize the reservation:

SQL > show parameter shared_pool_reserved_size

When there is a wrong value in request_misses, we need to increase the shared_pool_reserved_size.

This is the end of the content of "how to solve the ORA-04031 error of Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.