ORA-04031: unable to allocate 4128 bytes of shared memory
 (shared pool","NK_ERPRFX_CHARGETYPEKEY_XOLAP","mvobj part des","Allocate kctph[]/ckyph[]/ckyprt[] array") 
EDX_RPT_PYMNT_REFUND_FACT_X"

 -- Loop For all subpartitions for each part_table
               FOR j IN lc_subpart_arr.FIRST .. lc_subpart_arr.LAST
               LOOP
                  v_count := 999;

                  v_sql1 :=
                        'SELECT COUNT(1) FROM '
                     || v_part_tab
                     || ' SUBPARTITION ('
                     || lc_subpart_arr (j)
                     || ')';

                  EXECUTE IMMEDIATE (v_sql1)
                               INTO v_count;
                  --  DBMS_OUTPUT.put_line ('Vcount- ' || v_count);

                  IF v_count = 0
                  THEN
                     v_sql :=
                           'ALTER TABLE '
                        || v_part_tab
                        || ' DROP SUBPARTITION '
                        || lc_subpart_arr (j);

                     BEGIN
                        EXECUTE IMMEDIATE (v_sql);
                        --DBMS_OUTPUT.put_line (v_sql);
                        v_subpart_cnt := v_subpart_cnt +1;

SQL> select * from GV$SGAINFO order by inst_id;

   INST_ID NAME                                  BYTES RES
---------- -------------------------------- ---------- ---
         1 Free SGA Memory Available                 0
         1 Startup overhead in Shared Pool   721420288 No
         1 Maximum SGA Size                 2.2263E+10 No
         1 Granule Size                       16777216 No
         1 Streams Pool Size                  16777216 Yes
         1 Fixed SGA Size                      2123872 No
         1 Large Pool Size                   268435456 Yes
         1 Shared Pool Size                 7868514304 Yes
         1 Buffer Cache Size                1.3959E+10 Yes
         1 Redo Buffers                       14651392 No
         1 Java Pool Size                    134217728 Yes
         2 Startup overhead in Shared Pool   721420288 No
         2 Free SGA Memory Available                 0
         2 Maximum SGA Size                 2.2263E+10 No
         2 Granule Size                       16777216 No
         2 Streams Pool Size                  16777216 Yes
         2 Java Pool Size                    134217728 Yes
         2 Large Pool Size                   268435456 Yes
         2 Shared Pool Size                 7868514304 Yes
         2 Buffer Cache Size                1.3959E+10 Yes
         2 Redo Buffers                       14651392 No
         2 Fixed SGA Size                      2123872 No
         3 Fixed SGA Size                      2123872 No
         3 Redo Buffers                       14651392 No
         3 Buffer Cache Size                1.3959E+10 Yes
         3 Shared Pool Size                 7868514304 Yes
         3 Large Pool Size                   268435456 Yes
         3 Free SGA Memory Available                 0
         3 Streams Pool Size                  16777216 Yes
         3 Granule Size                       16777216 No
         3 Maximum SGA Size                 2.2263E+10 No
         3 Startup overhead in Shared Pool   721420288 No
         3 Java Pool Size                    134217728 Yes


SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 21232M
sga_target                           big integer 0

08:57:30 SQL> select * from V$SGA_TARGET_ADVICE;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       488		.5	    231 		  1		  22121
       732	       .75	    231 		  1		  22121
       976		 1	    231 		  1		  22121
      1220	      1.25	    231 		  1		  22121
      1464	       1.5	    231 		  1		  22121
      1708	      1.75	    231 		  1		  22121
      1952		 2	    231 		  1		  22121

7 rows selected.