Re: ORA-01555 snapshot too old problem.

saeedt7777_at_my-deja.com wrote in message <8u558n$93m$1_at_nnrp1.deja.com>...
>I have a query that needs to update ALL the rows in a large table.
>I am getting snapshot too old problem ORA-01555 error.
>What is a good technique to update all the rows in a large table
>without getting this error? Let's say I do not want to increase
>the rollback segments. Also the trick of opening and closing the
>cursor in my case will not work because I have to update all the rows
>in the table. Even if I delay the commit and do it every 5000
>times there is no guarantee that I will not hit the
>problem again.
>
>The question bolis down to one question. What is the sure and the
>safe way to update all rows of a large table in Oracle ?

Howard's solution is correct but doesn't satisfy your requirement that "I do not want to increase the rollback segments". Creating blocking transactions on your rollback segments ensures that they don't wrap round, so they'll grow instead. In addition, the technique to which Howard refers requires that you know the names of all the rollback segments in the database: this information is often not available to normal users: DBA's don't often make DBA_ROLLBACK_SEGS selectable by all users.

In short, there is no "sure and safe way to update all rows of a large table in Oracle" if you also want to restrict the size of your rollback segments.

You'll have to come up with something more imaginative. Some options:

1. If this is a batch job and you can guarantee that no-one is using the table at the same time, you could modify your PL/SQL to write your updated rows to a new table, committing every n thousand rows so your rollback segs don't grow too big (it won't use much rollback space anyway, because you'll only be doing inserts), then delete your old table and rename the new one, build indexes etc. Of course, you'll need enough room in your database to hold two copies of the table.
2. Again, if no-one is using your table, just write a simple bit of SQL:

     SELECT d. .......
            u.c_programname
            .....
       from t_user u,
            dw_user_dim d
      where d.c_userId != -1
        and u.c_id = d.c_userid;

   to produce a select with all the columns of the original table, and    the program_name substituted with the new value. Output this to    a file and then use SQL*Loader in direct mode to load it back in.    This won't use rollback segments at all.

3) If you must maintain access to your table whilst updating it,

   first create a table containing just the userid and rowid of    the rows from dw_user_dim, then modify your PL/SQL to read    through this table updating the dw_user_dim table (via the    saved rowids) and deleting the rows from the new table as you    go. Commit and re-open the cursor every n thousand rows. Note    that new rows added to the dw_user_dim table whilst this job    is processing won't be updated (but then, they wouldn't under    a simple SQL solution either).

4) Here's another one which works even if there are people accessing

   the table at the same time:

   create a new column: C_last_batch_update (date) on your dw_user_dim    table and modify user your PL/SQL as follows:

   DECLARE
   CURSOR ids_cursor IS

      SELECT rowid, c_userId
       FROM dw_user_dim
       WHERE c_userId != -1
         and c_last_batch_update != now;

   now date;
   commit_count number := 0;

   BEGIN      now := sysdate;

     open ids_cursor;

     loop

       fetch ids_cursor into ids_record;

       exit when ids_cursor%NOTFOUND;

       UPDATE dw_user_dim
          SET c_programName = (SELECT c_programName
                                 FROM t_user
                                 WHERE c_id = ids_record.c_userId),
              c_last_batch_update = now
         WHERE rowid = ids_record.rowid;

       commit_count := commit_count + 1;

       if commit_count > 2000
          commit;
          commit_count : = 0;
          close ids_cursor;
          open ids_cursor;
       end if;

   END LOOP;    commit;

   close ids_cursor;

   END;    The open and close of the cursor ensures that rollback segments    don't grow too big and that "snapshot too old" doesn't occur    and the new field c_last_batch_update ensures that the loop    finally terminates.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.