Undo tablespace issue

user_16850239 0
Viewed 405

I am trying to do a delete operation from multiple tables. I am deleting 500k records from almost 30 tables, each of which may or may not have records against the deletion criteria. Each table is fairly big, more than 1 GB.

    BEGIN
    ---Criteria for deletion 
    q_query:=' 
     SELECT a,
            b,
            c
     FROM T,
          GTY,
          GRUP,
          GART,
          T_category
      WHERE T.ID      =  GTY.ID
      AND GTY.ID      =  GRUP.ID
      AND GRUP.ID     =  GART.ID
      AND GART.ID     =  T_CATEGORY.ID;
    
     open c_cursor for q_query;
     --looping all the values in cursor for all the 30 tables.  
      loop fetch c_cursor into l_C,l_ID_V,l_ID_F;
      exit when c_cursor %NOTFOUND;
      
      
      begin
      DELETE FROM T_TABLE WHERE ID=L_ID AND ID_F=L_ID_F;
      .
      .
      .
      DELETE FROM T WHERE ID-L_ID AND ID_F=L_ID_F;
      end;
      end loop;
END;

I am running to into undo tablespace issue. ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'.

Things I have considered,

  1. Resize the undo tablespace

        alter  DATABASE datafile 'D:\ORADATA\MBSA\MBSA\DATAFILE\O1_MF_UNDOTBS1_xxx_.DBF'  autoextend on next 1G maxsize 32000M;

  2. Add commit at 10k count, with this approach I am running into

        ORA-01555: snapshot too old: rollback segment number string with name "string" too small

  3. Tried adding additional undo datafiles, but they were never considered.

  4. Increased undo retention : no impact

  5. I even tried making undo tablespace as big file, and it corrupted the database.

Is there any way to overcome this issue? Please help.Thanks!

Oracle version is 19c.

undo retention setting are :

temp_undo_enabled boolean FALSE    
undo_management   string  AUTO     
undo_retention    integer 1800     
undo_tablespace   string  UNDOTBS1
undo-redo plsql oracle sql
1 Answers

This won't be optimal since you won't be able to ROLLBACK; your changes but one solution is to add a COMMIT; in your loop juste before the end loop; :

    BEGIN
    ---Criteria for deletion 
    q_query:=' 
     SELECT a,
            b,
            c
     FROM T,
          GTY,
          GRUP,
          GART,
          T_category
      WHERE T.ID      =  GTY.ID
      AND GTY.ID      =  GRUP.ID
      AND GRUP.ID     =  GART.ID
      AND GART.ID     =  T_CATEGORY.ID;
    
     open c_cursor for q_query;
     --looping all the values in cursor for all the 30 tables.  
      loop fetch c_cursor into l_C,l_ID_V,l_ID_F;
      exit when c_cursor %NOTFOUND;
      
      
      begin
      DELETE FROM T_TABLE WHERE ID=L_ID AND ID_F=L_ID_F;
      .
      .
      .
      DELETE FROM T WHERE ID-L_ID AND ID_F=L_ID_F;
      end;
      COMMIT; --> HERE
      end loop;
END;
user_53412470
Related