Oracle Database Discussions

Hi,

When i run the create materialized view script, I get the ORA-04031: unable to allocate 59408 bytes of shared memory error.

SQL> @EXP_HR_UK_EMPLOYEE_DETAIL_MV.txt

Materialized view dropped.

select * from EXP_OBIEE.EXP_HR_UK_EMPLOYEE_DETAIL_V
                        *
ERROR at line 10:
ORA-04031: unable to allocate 59408 bytes of shared memory ("shared
pool","Select R.Rowid From Fnd_Conc...","Typecheck","qry_text : qcpisqt")


SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 1G

SQL> select 100-round(a.bytes/b.sm*100,2) pctused from
(select bytes from v$sgastat where name='free memory' AND pool='shared pool') a,
(select sum(bytes) sm from v$sgastat where pool = 'shared pool') b;
  2    3
   PCTUSED
----------
     77.13

SQL>  set lines  100
SQL>  set pages  999
SQL>
SQL>  column        c1     heading 'Pool |Size(M)'
SQL>  column        c2     heading 'Size|Factor'
SQL>  column        c3     heading 'Est|LC(M)  '
SQL>  column        c4     heading 'Est LC|Mem. Obj.'
SQL>  column        c5     heading 'Est|Time|Saved|(sec)'
SQL>  column        c6     heading 'Est|Parse|Saved|Factor'
SQL>  column        c7     heading 'Est|Object Hits'   format 999,999,999,999
SQL>
SQL>
SQL> SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,estd_lc_time_saved_factor c6,estd_lc_memory_object_hits c7 FROM v$shared_pool_advice;


                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved              Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor      Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ----------------
       576      .5625        118       6239  109079448      .9987    1,516,446,112
       688      .6719        202       9103  109165713      .9995    1,516,574,999
       800      .7813        256      10656  109198135      .9998    1,516,595,310
       912      .8906        311      12386  109210013      .9999    1,516,602,918
      1024          1        366      14074  109217855          1    1,516,607,827
      1136     1.1094        421      15455  109222296          1    1,516,611,022
      1248     1.2188        476      16866  109227442     1.0001    1,516,613,599
      1360     1.3281        529      17868  109230835     1.0001    1,516,616,451
      1472     1.4375        584      18323  109231123     1.0001    1,516,619,020
      1584     1.5469        639      19468  109231150     1.0001    1,516,620,727
      1696     1.6563        694      20716  109232515     1.0001    1,516,622,106
      1808     1.7656        749      22376  109237627     1.0002    1,516,623,331
      1920      1.875        804      23485  109237656     1.0002    1,516,624,641
      2032     1.9844        859      24847  109240084     1.0002    1,516,625,794
      2144     2.0938        911      25893  109241459     1.0002    1,516,626,987

15 rows selected.

Details:
OS:Red Hat Linux 5.2
DB:Oracle 10g (10.2.0.4)

What should be done?

Regards,
SAM