We can see that INDX5 and DBMS_LOB cause page feed (that is, page feed caused by lob object processing involved in exp), swap out unused memory recently, but swap out the memory and merge fragments in
We can see that INDX = 5, DBMS_LOB results in page feed (that is, page feed caused by lob object processing involved in exp), swap out the memory that is not used recently, but swap out the memory and merge fragments in
-- 1. error message:
> EXP-00008: Oracle error 4031 encountered
> ORA-04031: unable to allocate 4096 bytes of shared memory
("Shared pool", "BEGIN: EXEC_STR: = SYS. DBMS...", "PL/SQL MPCODE", "BAMIMA: Bam Buffe
-- II. Cause of error:
-- The shared memory is too small, there are some fragments, and there is no effective use of the reserved area, resulting in the failure to allocate a suitable shared area.
-- 3. Solution steps:
-- 1. view the current environment
SQL> show sga Total System Global Area 566812832 bytes
Fixed Size 73888 bytes Variable Size 28811264 bytes
Database Buffers 536870912 bytes Redo Buffers 1056768 bytes
Show parameter shared_pool
Name type value: shared_pool_reserved_size string 1048576.
Shared_pool_size string 20971520.
SQL> select sum (free_space) from v $ shared_pool_reserved;
SUM (FREE_SPACE): 1048576
-- We can see that the reserved zone is not properly used.
-- 2. View reserved area usage
SQL> SELECT FREE_SPACE, FREE_COUNT, REQUEST_FAILURES, REQUEST_MISSES, LAST_FAILURE_SIZE
From v $ SHARED_POOL_RESERVED;
FREE_SPACE FREE_COUNT REQUEST_FAILURES REQUEST_MISSES LAST_FAILURE_SIZE
-------------------------------------------------------------------
1048576 1 146 0 4132
-- The size of the shared area required by the object for the last failed application for a shared area is 4132.
Select name from v $ db_object_cache where sharable_mem = 4132;
Name: dbms_lob
-- 3. Analyze the usage of each Shared Pool
SQL> select KSPPINM, KSPPSTVL from x $ ksppi, x $ ksppcv
Where x $ ksppi. indx = x $ ksppcv. indx and KSPPINM = '_ shared_pool_reserved_min_alloc ';
KSPPINM KSPPSTVL
---------------------------------------
_ Shared_pool_reserved_min_alloc 4400 -- (door value)
-- We can see that INDX = 5, DBMS_LOB results in page feed (that is, page feed caused by lob object processing involved in exp), and swap out the memory that has not been used recently, however, after the memory is swapped out and the shards are merged, there is still no suitable zone for storing data in the shared zone. This means that the shared zone is small and there are too many shards. Then, you can apply for a reserved zone based on the _ shared_pool_reserved_min_alloc, the door value is 4400, so it does not meet the requirements for applying for a reserved area, resulting in a 4031 error. We have seen that all reserved areas are idle, so we can reduce the threshold value so that more objects applying for shared memory smaller than 4400 can apply for a reserved area without causing a 4031 error.
-- 4. solution:
-- 1). Increase the shared_pool (not suitable when the server is not DOWN)
-- 2). patch (not suitable when the machine is not DOWN)
-- 3). Reduce the door value (not suitable when the machine is not DOWN)
-- Because LAST_FAILURE_SIZE <_ shared_pool_reserved_min_alloc, it indicates that the reserved zone is not effectively used.
SQL> alter system set "_ shared_pool_reserved_min_alloc" = 4000;
Alter system set & quot; _ shared_pool_reserved_min_alloc & quot; = 4000
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
-- 9i usage alter system set shared_pool_reserved_min_alloc "= 4000 scope = spfile;
-- 4). Use alter system flush shared_pool; (the problem cannot be solved fundamentally)
-- 5). Use dbms_shared_pool.keep