How to forcibly flush the undo log/tablespace of UNDOTBS1

oracleplsql

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me the UNDOTBS1 tablespace not large enough.

I'm aware of something like CHECKPOINT NUM, which normally allows you to flush the undo logs at the given NUM. I was wondering if there was a similar functionality for anonymous blocks in PL/SQL given that I do not have the rights to modify UNDOTBS1.

    SET serveroutput ON;
    DECLARE
      --Cursor declaration
      CURSOR a_cur IS
      SELECT * FROM aTable
      FOR UPDATE;

      --Cursor row  
      a_rec a_cur%ROWTYPE;

    BEGIN
      -- TABLE
     OPEN a_cur;
      LOOP
       FETCH a_cur INTO a_rec;
       EXIT WHEN a_cur INTO %NOTFOUND;

       --Update the record
       UPDATE aTable SET 
         field1 = 'aVal',
         field2 = 'aVal',
         field3 = 'aVal'    
       WHERE CURRENT OF a_cur;

       -- Attempting to force flush the UNDOTBS1 since it is not large enough
       --COMMIT;

      END LOOP;
      CLOSE a_cur;
      COMMIT;
    END;

Best Answer

Undo data is used to ensure ACID of the database. It supports your transaction and cannot be removed until you committed or rolled it back. Read "What Is Undo?" in Database Administrator's Guide.

You can however commit your transaction to release some undo space, but you lose the ability to roll back your changes.

If the DML you are performing requires more undo space because there are a lot of records updated/deleted/inserted and your undo tablespace is not sized correctly, DML will fail, because Oracle cannot allocate extents to support your transaction.

Related Question