Undo tablespace issue

Question

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,

Resize the undo tablespace

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

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

Tried adding additional undo datafiles, but they were never considered.
Increased undo retention : no impact
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

Are you deleting from one of the tables that you are querying in the outer loop? How long does this process run? What is your undo_retention setting? What version of Oracle are you using? Not sure why you're apparently using dynamic PL/SQL (and the string you posted doesn't appear to be a valid PL/SQL block). — Justin Cave
There are two possible solutions: 1) use select ... bulk collect ... to fill PL/SQL collection variable and then use forall with delete to use bulk operations. They are hundred times faster and you do not need to hold a single cursor for hours, which introduces unnecessary contention. — astentx
2) Alternatively build separate auxiliary table with all the data you need as a source of deletion criteria and use dbms_parallel_execute which allows chunked execution: build chunks by record number or rowid and run in parallel or sequentially with controlled degree of parallelism and contention. — astentx
split and conquer. the undo tablespace is there to allow transactions be able to rollback always to the initial state. That is one of the ACID principles that any database must fulfil. When you are getting the first error, it is clear that you don't have enough undo tablespace to handle all those deletes. When you get the ORA-1555, Oracle is telling you that there is no possibility to go back to the original state in case of rollback, therefore the transaction is terminated, precisely to protect the integrity of the database. — Roberto Hernandez
Split and conquer, as I said, is the strategy here. If you don't want to recreate elements, you don't need do. Best strategy is to use temporary tables to store the data you want to keep, then truncate source table and insert by direct path back to the original table. As you said, is offline operation, so you have no issues in running direct path operations, as they lock the table. — Roberto Hernandez

SuperPoney SuperPoney · Accepted Answer · 2021-10-26T13:57:09

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;

Undo tablespace issue

1 Answers