Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
I'm surprised that commit in there worked at all, I thought that wouldn't work with an open "for update" cursor. Can't you do your update in plain SQL? –  Mat May 23 '13 at 10:26
    
unfortunately, I can't... what doesn't show in my example is the fact that when I do field = 'aVal', I actually do the following: field1 = FN_FUNCTION_VAL('aVal'). That's why I need the WHERE CURRENT OF a_cur. –  DFIVE May 23 '13 at 11:19
add comment

migrated from stackoverflow.com May 23 '13 at 9:56

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
I'm fully aware of ACID... and that UNDO is used for rollbacks...that doesn't answer my question and I already attempted doing a commit... –  DFIVE May 23 '13 at 7:52
    
Undo is always generated, you cannot avoid it. Limiting the rows to be updated using WHERE clause could do the thing–you would execute a series of DML statements updating the small portions of a table, but as you may guess, it breaks A, and C of ACID in that someone may update the table while you update another subset of rows. –  Yasir Arsanukaev May 23 '13 at 8:01
    
I am not attempting to avoid the UNDO nor am I debating that we should not have UNDOTBS1. I want to force the flush of the UNDO table by forcing a commit somewhere or after I finished handling a certain amount of data (hence the idea of CHECKPOINT). As for the WHERE clause, I am not trying to limit the extent of my corrections, I am doing it in a massive scale. Again, that's not the point, I am looking for a functionality or a feature... not debating the laws of Database concepts. Unless maybe you can explain why the COMMIT (which I commented) failed to flush the undo logs? –  DFIVE May 23 '13 at 8:21
    
What does "failed" mean? Does Oracle return some error? –  Yasir Arsanukaev May 23 '13 at 8:35
    
The infamous : ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1 –  DFIVE May 23 '13 at 8:37
show 2 more comments

Here's the correct code you need:

Update aTable
Set
  field1 = 'aVal',
  field2 = 'aVal',
  field3 = 'aVal'
Where
  coalesce(field1,'x') != 'aVal' or
  coalesce(field2,'x') != 'aVal' or
  coalesce(field3,'x') != 'aVal'

This will generate less undo than your cursor-based method.

If you cannot size your undo tablespace appropriately, which is the real fix for the problem, then run this with a large rownum < x limit following each execution with a commit. Keep running until you've modified all the data.

A better approach might be to create a new table with the data you want using DBMS_Redefinition and a nologging create-table-as-select.

share|improve this answer
    
yeah... I already did that work around by creating a cursor with rownum less than 200000, which worked... but I had to do it several times... (not elegant at all). Creating a new table is not an option for me (although I did think about it) since I don't have the necessary privileges. I have to make this a deliverable... I wished I had something more elegant... –  DFIVE May 23 '13 at 15:43
    
Don't do it as a cursor though, just as a regular SQL statement. –  David Aldridge May 23 '13 at 16:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.