8 Replies Latest reply: May 2, 2014 4:54 AM by fac586 RSS

    ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX

    hemu

      hi

      i have a following query

      it should return 12951 records

      however in apex it is returning only 500 records

      with a error msg as titled

      declare
      vsql varchar2(4000);
      begin
      apex_debug.enable(9);
      vsql:=
      'select branch,br_name(branch) Branch_Name,BAL_DATE1 ,BRCODE,ACNO,NAME,SANC_DATE,EFF_DATE,
      INST_DATE,EXP_DATE,RATE,LIMIT,DP,PRN_OST,INT_OST,TOTALOS,PRN_OVD,INT_OVD,RATING,t_cust(branch,prdacctid) custno  ,';
      vsql:=vsql||'(case when abs(to_number(PRINCIPALO)) <'||:P314_BKT1 ||' then ' ||chr(39)||'1_Less_than_'||:P314_BKT1||chr(39)||
      ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT1 ||' and '||:P314_BKT2 ||' then '||chr(39)||'2_'||:P314_BKT1||'_to_'||:P314_BKT2 ||'_lac'||chr(39)||
      ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT3 ||' and '||:P314_BKT4 ||' then '||chr(39)||'3_' ||:P314_BKT3||'_to_'||:P314_BKT4||'_lac'||chr(39)||
      ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT5 ||' and '||:P314_BKT6 ||' then '||chr(39)||'4_' ||:P314_BKT5||'_to_'||:P314_BKT6||'_lac'||chr(39)||
      ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT7 ||' and '||:P314_BKT8 ||' then '||chr(39)||'5_' ||:P314_BKT7||'_to_'||:P314_BKT8||'_lac'||chr(39)||
      ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT9 ||' and '||:P314_BKT10||' then '||chr(39)||'6_' ||:P314_BKT9||'_to_'||:P314_BKT10||'_lac'||chr(39)||
      ' when abs(to_number(PRINCIPALO))  > '||:P314_BKT10 ||' then '||chr(39)||'7_above_'||:P314_BKT10||'_lac '|| chr(39)||
      ' end) as remark from alm_test where bal_date1= to_date('||chr(39)||:P314_ASON ||chr(39)||','||chr(39)||'dd-mon-yyyy'||chr(39)||')  and t_module_new(prdacctid,branch) in (30)'||
      ' and branch between '|| to_number(:P314_FROMBRANCH )|| ' and ' || to_number(:P314_TOBRANCH) || ' order by branch ';
      apex_debug.log_long_message(vsql, true);
      return vsql;
      end;
      
      
      
      
      debug page has returned following query.
      
      

      select branch,br_name(branch) Branch_Name,BAL_DATE1 ,BRCODE,ACNO,NAME,SANC_DATE,EFF_DATE, INST_DATE,EXP_DATE,RATE,LIMIT,DP,PRN_OST,INT_OST,TOTALOS,PRN_OVD,INT_OVD,RATING,t_cust(branch,prdacctid) custno ,(case when abs(to_number(PRINCIPALO)) <0 then '1_Less_than_0' when abs(to_number(PRINCIPALO)) between 0 and 2500000 then '2_0_to_2500000_lac' when abs(to_number(PRINCIPALO)) between 2500001 and 5000000 then '3_2500001_to_5000000_lac' when abs(to_number(PRINCIPALO)) between 5000001 and 10000000 then '4_5000001_to_10000000_lac' when abs(to_number(PRINCIPALO)) between 10000001 and 15000000 then '5_10000001_to_15000000_lac' when abs(to_number(PRINCIPALO)) between 15000001 and 99999999 then '6_15000001_to_99999999_lac' when abs(to_number(PRINCIPALO)) > 99999999 then '7_above_99999999_lac ' end) as remark from alm_test where bal_date1= to_date('31-Mar-2014','dd-mon-yyyy') and t_module_new(prdacctid,branch) in (30) and branch between 2 and 212 order by branch

      having apex 4.2.3 EPG

      and

      oracle version

      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

      PL/SQL Release 11.1.0.7.0 - Production

      CORE    11.1.0.7.0      Production

      TNS for 32-bit Windows: Version 11.1.0.7.0 - Production

      NLSRTL Version 11.1.0.7.0 - Production

       

      is there anything wrong with query?

      please help

        • 1. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
          scott.wesley

          Sounds like you have three things to consider

          1) Your APEX tablespace is full and needs to be extended.

          2) You have a Maximum Row Count in your report region attributes of 500.

          3) Your sql will perform badly over time due to a lack of bind variables

          • 2. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
            fac586

            hemu wrote:

             

            i have a following query

            it should return 12951 records

            however in apex it is returning only 500 records

            Assuming this query is the source for a standard report region (always specify the region type), this is due to the default setting of the report's Maximum Row Count attribute. When this attribute is not explicitly specified it defaults to 500. See the online help for the attribute and set it to a suitable value for your application.


            12951 is too many rows for anyone to work their way through page-by-page, and trying to display them all on a single page is definitely not a good idea. Does the application provide a filtering mechanism to reduce the number of rows returned to a manageable level?

            with a error msg as titled

            ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX

            The error message is self-explanatory: there is no space remaining on the APEX tablespace. Contact your DBA, instance admin, or hosting provider to increase the space available.

            • 3. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
              fac586

              scott.wesley wrote:

               

              3) Your sql will perform badly over time due to a lack of bind variables

              That sounds familiar...

               

              It would also be a good idea to reduce the size of the debug trace generated by resetting the debug level to the default at the end of the block:

               

              declare 
              vsql varchar2(4000); 
              begin 
              apex_debug.enable(apex_debug.c_log_level_engine_trace); 
              vsql:= 
              'select branch,br_name(branch) Branch_Name,BAL_DATE1 ,BRCODE,ACNO,NAME,SANC_DATE,EFF_DATE, 
              INST_DATE,EXP_DATE,RATE,LIMIT,DP,PRN_OST,INT_OST,TOTALOS,PRN_OVD,INT_OVD,RATING,t_cust(branch,prdacctid) custno  ,'; 
              vsql:=vsql||'(case when abs(to_number(PRINCIPALO)) <'||:P314_BKT1 ||' then ' ||chr(39)||'1_Less_than_'||:P314_BKT1||chr(39)|| 
              ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT1 ||' and '||:P314_BKT2 ||' then '||chr(39)||'2_'||:P314_BKT1||'_to_'||:P314_BKT2 ||'_lac'||chr(39)|| 
              ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT3 ||' and '||:P314_BKT4 ||' then '||chr(39)||'3_' ||:P314_BKT3||'_to_'||:P314_BKT4||'_lac'||chr(39)|| 
              ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT5 ||' and '||:P314_BKT6 ||' then '||chr(39)||'4_' ||:P314_BKT5||'_to_'||:P314_BKT6||'_lac'||chr(39)|| 
              ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT7 ||' and '||:P314_BKT8 ||' then '||chr(39)||'5_' ||:P314_BKT7||'_to_'||:P314_BKT8||'_lac'||chr(39)|| 
              ' when abs(to_number(PRINCIPALO)) between '||:P314_BKT9 ||' and '||:P314_BKT10||' then '||chr(39)||'6_' ||:P314_BKT9||'_to_'||:P314_BKT10||'_lac'||chr(39)|| 
              ' when abs(to_number(PRINCIPALO))  > '||:P314_BKT10 ||' then '||chr(39)||'7_above_'||:P314_BKT10||'_lac '|| chr(39)|| 
              ' end) as remark from alm_test where bal_date1= to_date('||chr(39)||:P314_ASON ||chr(39)||','||chr(39)||'dd-mon-yyyy'||chr(39)||')  and t_module_new(prdacctid,branch) in (30)'|| 
              ' and branch between '|| to_number(:P314_FROMBRANCH )|| ' and ' || to_number(:P314_TOBRANCH) || ' order by branch '; 
              apex_debug.log_long_message(vsql, true); 
              apex_debug.enable(apex_debug.c_log_level_info); 
              return vsql; 
              end; 
              

               

              Even better would be to check the generated query by parsing it using DBMS_SQL and only logging the query text if the parse fails...

              • 4. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
                fac586

                hemu wrote:

                 

                is there anything wrong with query?

                Yes. It doesn't use bind variables and it doesn't need to be dynamically generated.

                • 6. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
                  hemu

                  sir

                  sorry to bother you again

                  what i am trying is to generate remark column dynamically

                  the report contains 5 pairs of page items and user is allowed to input any number in it

                  that's why i think i have to use dynamic sql ....i think so (may be a wrong approach but still learning)

                  thanx again

                  • 7. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
                    fac586

                    hemu wrote:

                     

                    what i am trying is to generate remark column dynamically

                    the report contains 5 pairs of page items and user is allowed to input any number in it

                    that's why i think i have to use dynamic sql ....i think so (may be a wrong approach but still learning)

                    Dynamic SQL is unnecessary to do this. The page items can be referenced as bind variables in a static query.

                     

                    Dynamic SQL is only necessary if the projection, data source or predicates are not known until runtime.

                    • 8. Re: ORA-01654: unable to extend index APEX_040200.WWV_FLOW_DEBUG_MESSAGES_IDX1 by 128 in tablespace APEX
                      hemu

                      sir thanxxxxxxx

                       

                      The page items can be referenced as bind variables in a static query.

                      select branch,br_name(branch) Branch_Name,BAL_DATE1 ,BRCODE,ACNO,NAME,SANC_DATE,EFF_DATE,
                      INST_DATE,EXP_DATE,RATE,LIMIT,DP,PRN_OST,INT_OST,TOTALOS,PRN_OVD,INT_OVD,RATING,t_cust(branch,prdacctid) custno  ,
                      (case 
                        when abs(to_number(PRINCIPALO)) <:P314_BKT1  then '1_Less_than_'||:P314_BKT1
                        when abs(to_number(PRINCIPALO)) between :P314_BKT1  and :P314_BKT2  then '2_'||:P314_BKT1||'_to_'||:P314_BKT2 ||'_lac'
                        when abs(to_number(PRINCIPALO)) between :P314_BKT3 and :P314_BKT4  then '3_' ||:P314_BKT3||'_to_'||:P314_BKT4||'_lac'
                        when abs(to_number(PRINCIPALO)) between :P314_BKT5 and :P314_BKT6  then '4_' ||:P314_BKT5||'_to_'||:P314_BKT6||'_lac'
                        when abs(to_number(PRINCIPALO)) between :P314_BKT7 and :P314_BKT8  then '5_' ||:P314_BKT7||'_to_'||:P314_BKT8||'_lac'
                        when abs(to_number(PRINCIPALO)) between :P314_BKT9 and :P314_BKT10 then '6_' ||:P314_BKT9||'_to_'||:P314_BKT10||'_lac'
                        when abs(to_number(PRINCIPALO))  > :P314_BKT10 then '7_above_'||:P314_BKT10||'_lac '
                        end) as remark from alm_test 
                      where bal_date1= to_date(:P314_ASON ,'dd-mon-yyyy') and t_module_new(prdacctid,branch) in (30)
                      and branch between to_number(:P314_FROMBRANCH ) and  to_number(:P314_TOBRANCH) 
                      order by branch 
                      

                      it is working alright