Re: Snapshot too old but UNDO_RETENTION very high

From: DBA Infopower Support <support_at_dbainfopower.com>
Date: Fri, 6 Feb 2004 21:47:38 -0800
Message-ID: <MLmdnWVzg-Qa57ndRVn-tA_at_comcast.com>


[Quoted] Hello Daniel,

[Quoted]   Please correct me if I am wrong:

     User complains, that while running lengthy update it fails with Ora-1555.

      Amount of rows is 9M rows and considering that other transactional activity on database continues it is very probably that consistent information in rollback segment will be overwritten and 1555 error would be generated.

    having encountered similar situation multiple times in production environment, we always choose PL/SQL block with the batch commits to single update statement. This technique allows to fast commit small amount of rows at time and greatly reduces probability of 1555 during the update.

    I.e. For example: instead of "update table T set F1 = ... where ... " to use

    declare

      cursor s1 is select rowid rid from T where ...
      n number;
   begin
      for s1rec in s1 loop
        update T set ... where rowid = s1rec.rid;
        n := n + 1;
        if ( mod(n,100) = 0 ) then
          commit;
        end if;
      end loop;
      commit;

   end;
   /
Please, let us know if you have any additional question. Regards,

Support

DBA Infopower

https://www.dbainfopower.com

The advice provided by DBA Infopower are provided "as is" with no warranty. DBA Infopower expressly disclaims any warranty, regarding the advice including any implied warranty of merchant fitness for a particular purpose aviating course of dealing and/or performance. DBA Infopower does not warrant that the advices provided by DBA Infopower will be free from bias, detests, errors, eavesdropping or listening. DBA Infopower shall not be responsible for the quality of information or the authentication of the services or details given by experts on the advice. By using this advice the user of the advice accepts the terms and conditions of this statement.

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1076118472.975179_at_yasure...
> Michel Cadot wrote:
>
> >>You are just plain and utterly wrong, andapparently you don't know at
> >>all how the rollback mechanism works.
>
> >>Sybrand Bakker, Senior Oracle DBA
> >
> >
> > Sybrand, I agree completely with you.
> > The answers of Mr DBA Infopower Support are among the worst ones on this
newsgroup.
> >
> > Michel Cadot
>
> Well it appears that we all agree on one thing ... Tech Support at
> InfoPower is of the same quality as technical support just about
> everywhere else.
>
> Quick with an answer. Rarely accurate or useful. ;-)
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Sat Feb 07 2004 - 06:47:38 CET

Original text of this message