URGENT: SQL error 1632 occurred when accessing tab...

former_member810660 · ‎09-06-2007

Hi Experts,

i was getting this error <b>"SQL error 1632 occurred when accessing table"</b> when updating a custom table from a custom program in the back-ground. i have gone through lot of SAP Notes and forum pages what i found is, when using INSERT instead of MODIFY. my code is working fine when the extraction of records by the program is less, jobs are running successfully. i will copy my code below and dump information.

in the sap notes it was suggested to increase the table space from basis side. in my technical settings of the table i used data class as 2 (3,400 to 13000 records) if i increase this size, will it effect when its crosses the this limit is it anywhere related to this error.

Should i got for INSERT to MODIFY or change in size of table from basis side.

can anyone analyze this error and give me some solution. Points will be awarded for all answers.Thanks in advance.

Check the below dump:


Database error text........: "ORA-01632: max # extents (450) reached in index      
 SAPR3.ZSCBTWTRFL~001#"                                                            
Internal call code.........: "[RSQL/INSR/ZSCBTWTRFL ]"                             
Please check the entries in the system log (Transaction SM21).                                                                                
If the error occurred in a non-modified SAP program, you may be                    
able to find a solution in the SAP note system.                                    
If you have access to the note system yourself, use the following                  
search criteria:                                                                                
"DBIF_RSQL_SQL_ERROR"                                                             

Source code extract 

007050   *&                                                                     *    
007060   *&      Form  i_update_table                                                
007070   *&                                                                     *    
007080   *                                                                      *    
007090     FORM i_update_table.                                                      
007100                                                                                
007110       LOOP AT i_finaltable.                                                   
007120                                                                                
007130   * Clear Work Areas.                                                         
007140         CLEAR: i_vbrk,                                                        
007150                wa_zscbtwtrfl,                                                 
007160                wa_zscbtwtrfl1.                                                
007170                                                                                
007180         READ TABLE i_vbrk WITH KEY vbeln = i_finaltable-vbeln                 
007190                                                BINARY SEARCH.                 
007200         IF sy-subrc = 0.                                                      
007210                                                                                
007220           READ TABLE i_zscbtwtrfl INTO wa_zscbtwtrfl WITH KEY                 
007230                            vbeln = i_vbrk-sfakn BINARY SEARCH.                
007240                                                                                
007250           READ TABLE i_zscbtwtrfl INTO wa_zscbtwtrfl1 WITH KEY                
007260               vbeln = i_vbrk-sfakn fkart = text-026 BINARY SEARCH.            
007270                                                                                
007280           IF i_finaltable-fkart = text-026                                    
007290                  AND  i_finaltable-oic_mot = '07'.                            
007300   *Pass modified date to ZSCBTWTRFL table.             
007310             i_finaltable-modat = sy-datum.             
007320   *Insert New Record into database table ZSCBTWTRFL    
-------->             INSERT zscbtwtrfl FROM i_finaltable.       
007340                                                        
007350           ELSEIF i_finaltable-fkart = text-026         
007360                    AND i_vbrk-konda = '06'.            
007370   *Pass modified date to ZSCBTWTRFL table.             
007380             i_finaltable-modat = sy-datum.             
007390   *Insert New Record into database table ZSCBTWTRFL    
007400             INSERT zscbtwtrfl FROM i_finaltable.       
007410                                                        
007420           ELSEIF i_finaltable-fkart = text-024         
007430                    AND i_vbrk-konda = '06'             
007440                    AND i_finaltable-matnr NE text-017. 
007450   *Pass modified date to ZSCBTWTRFL table.             
007460             i_finaltable-modat = sy-datum.             
007470   *Insert New Record into database table ZSCBTWTRFL    
007480             INSERT zscbtwtrfl FROM i_finaltable.       
007490                                                        
007500           ELSEIF i_finaltable-fkart = text-023.        
007510                                                        
007520             IF wa_zscbtwtrfl-fkart = text-026.         

Active calls / events                                                                                
No.... Type........ Name..........................                  
       Programm                                                     
       Include                                  Line                                                                                
1 FORM         I_UPDATE_TABLE                                  
       ZSIOPITD                                                     
       ZSIOPITD                                   733               
     2 EVENT        START-OF-SELECTION                              
       ZSIOPITD                                                     
       ZSIOPITD                                   100                                                                                
Internal notes                                                                                
The termination occurred in the function "ExecuteCall" of the SAP   
Basis System, specifically in line 5829 of the module               
 "//bas/46D/src/krn/runt/absapsql.c#22".                            
The internal operation just processed is "SQLS".                    
The internal session was started at 20070905100126.                 
Internal call code.........: "[RSQL/INSR/ZSCBTWTRFL ]"              

Active calls in SAP kernel                                                                                
=> 32 bit R/3 Kernel                
 => 64 bit AIX Kernel                
 => Heap limit      = 134217728      
 => Stack limit     = unlimited      
 => Core limit      = 1073741312     
 => File size limit = unlimited      
 => Heap address  = 0x2602a020       
 => Stack address = 0x2ff1a0e0       
 => Stack low     =  0x2ff1a0e0      
 => Stack high    =  0x2ff22ae0      

ABAP control blocks CONT                                                                                
Include                                 Line source code                       
Index Name F1 Co Par01 Par2. Par3. Par4. Tabl                                                                                
ZSIOPITD                                  723 vbeln = i_vbrk-sfakn BINARY SE   
 2130 PAR2 80 79  149    342                                                   
ZSIOPITD                                  726 vbeln = i_vbrk-sfakn fkart = t   
 2132 TREA 14 79  78     87    2      2                                        
 2136 PAR2 80 79  149    342                                                   
 2138 PAR2 81     150   V12                                                    
ZSIOPITD                                  729 AND i_finaltable-oic_mot = '07   
 2140 COMP 00     190   V12                                                    
 2142 BRAF 02     3                                                            
 2143 comp 00 78  223    343                                                   
 2145 BRAF 02     8                                                            
ZSIOPITD                                  731 i_finaltable-modat = sy-datum.   
 2146 mvq2 08  5  2124   724                                                   
ZSIOPITD                                  733 INSERT zscbtwtrfl FROM i_final   
 2148 SQLS 0B                                                                  
 2149 SQLS 00     40                                                           
 2150 NOP1 00    V2                                                            
>>>>> SQLS 0E     81                                                           
ZSIOPITD                                  736 AND i_vbrk-konda = '06'.         
 2152 BRAX 00 Branch to 3055                                                   
 2153 COMP 00     190   V12                                                    
 2155 BRAF 02     3                                                            
 2156 comp 00 78  346    347                                                   
 2158 BRAF 02     8                                                         
ZSIOPITD                                  738 i_finaltable-modat = sy-datum.
 2159 mvq2 08  5  2124   724                                                
ZSIOPITD                                  740 INSERT zscbtwtrfl FROM i_final
 2161 SQLS 0B

Thanks & Regards,

Poorna.

Former Member · ‎09-06-2007

Reduce the amount of data you are inserting (reduce the size of i_finaltable) or do a database commit every few thousand or so records.

Be aware that if you do commits, you may have problems when you try to restart the program if it fails.

On second thought, it looks like a sizing problem. Before trying the above try increasing the size category of zscbtwtrfl.

Rob

Message was edited by:

Rob Burbank

former_member810660 · ‎09-07-2007

Hi Rob,

From your mail i understood that, there is not need to change the technical settings of the table, just do a commit work after each 1000 records by a condition or by using modify statement.

i have a doubt here, if i do a commit work after each 1000 records, thats means table will be updated with the records, if in case the job is still failed due some other reason (code), which comes after my commit is finished. will the database records which are previously updated by commit will be rolled back or the records will be available still in the table.

I have not yet implemented the solution, becos if the program is run for 1 days its going successful but for more days its failing with this error, and some time even for day with huge data is failing.

Give me some suggestions on this.

Thanks & Regards,

Poorna.

Former Member · ‎09-07-2007

In my earlier reply, I indicated that the first solution I provided might not work and would be dangerous.

The second solution (just changing the technical settings would be a lot simpler and less dangerous. It might not work, but it won't cost you to try. (If it doesn't, don't forget to reset those settings.)

Rob

Former Member · ‎09-07-2007

Poorna,

If you have an Oracle DBA available to you, then please ask him to look at the STORAGE parameters of the index, SAPR3.ZSCBTWTRFL~001#

From the Oracle side, it is a simple fix to drop and re-create the index with bigger INITIAL and NEXT values. It could be that adjusting the size category (as Rob suggests) will do the same thing.

Mike

former_member810660 · ‎09-07-2007

Hi Rob and Michael,

Thanks for all your suggestions.

Thanks & Regards,

Poorna.

former_member810660 · ‎09-07-2007

Thanks for all your suggestions.

former_member810660 · ‎09-07-2007

Hi Michael & Rob,

In my custom table i have index ZSCBTWTRFL-001 "Index ZSCBTWTRFL~001 exists in database system ORACLE" which is a secondary index. this is a non unique index with index on all database systems with 13 fields added in the list. is it the right way.

As said by michael, its creating problem with the index.

Give me some suggestions on this.

Thanks & Regards,

Poorna.

Former Member · ‎09-07-2007

Thirteen fields is a big index. Do you need all of the fields? You can remove the index and run your update and then try to recreate the index.

But I'd really think about whether you need either the index or all of these fields at all.

Rob

Former Member · ‎09-07-2007

Since it's defined in the SAP dictionary, you don't want to do this at the database level. If you decide to remove the index, go to SE11 and select the table. On the next screen press the "Indexes" button and select the index. Then select the "no database index" radio button, save and activate. the index will be removed, but you can recreate it later by selecting the "index on all database sytems" radio button.

Rob

former_member810660 · ‎09-07-2007

Hi Michael & Rob,

In my custom table i have index ZSCBTWTRFL-001 "Index ZSCBTWTRFL~001 exists in database system ORACLE" which is a secondary index. this is a non unique index with index on all database systems with 13 fields added in the list. is it the right way.

As said by michael, its creating problem with the index.

Give me some suggestions on this.

Thanks & Regards,

Poorna.

Former Member · ‎09-07-2007

Poorna,

The ORA-01632 problem is this:

1. Oracle allocates extents to grow the index. The index starts with one extent, and when that extent fills, another extent is allocated. These size of these allocations are controlled by three parameters of the STORAGE clause used when the index is created: INITIAL, NEXT, and PCTINCREASE.

2. You reached the limitation on the number of extents for that index, 450. Please see the options listed under "Example 4" at the following page to see how to fix this from the Oracle side:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schema007.htm

Something as simple as:

ALTER INDEX ZSCBTWTRFL~001#

REBUILD ONLINE

STORAGE

( INITIAL 100m NEXT 20m PCTINCREASE 0 MAXEXTENTS 1000);

might fix it for you, but check with your DBA first.

There are a couple of other things to look at if you do not have a DBA or if you would rather solve this from the SAP side:

1. Is it really necessary to include all fields in the index? Does your program benefit from it? If you are not using all the fields to do an indexed lookup, then do not include unused fields in the index.

2. Have you tried changing the table's technical settings to a higher expected size?

Mike

former_member810660 · ‎09-09-2007

Thanks for all your suggetions

URGENT: SQL error 1632 occurred when accessing table