Date: Tue, 17 Feb 2004 15:29:09 -0700
From: "Daniel W. Fink" <Daniel.Fink@Sun.COM>
Subject: Re: ORA-01650, one idea
To: oracle-l@freelists.org
Message-id: <403295B5.159FEEC6@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
References: <20040217175442.75065.qmail@web21411.mail.yahoo.com>
 <40326088.D5F945AA@sun.com> <005801c3f59e$7afaf1d0$2501a8c0@dazasoftware.com>
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

Logging only impacts redo and not undo. Regardless of the logging status, you
still need to be able to rollback the transaction.

Juan Cachito Reyes Pacheco wrote:

> 1)
> If you set logging off the table and their index (and disable foreing keys
> to this table)?,
> does it help some or not help.
>
> Tell us if this improve your performance in some way.
> ALTER TABLE GRUPMONCLIE
>
> NOLOGGING
>
> /
>
> ALTER INDEX GRUPMONCLIE  REBUILD
>
> NOLOGGING
>
> / or drop indexes and rebuild them after
>
> 2) If your are deleting 99% of the table
>
> you coul
>
> create table y as select from x;
>
> drop x
>
> rename y to x;
>
> Don't forget the full backup after this and to restore back the logging mode
>
> ----- Original Message -----
> From: "Daniel Fink" <Daniel.Fink@Sun.COM>
> To: <oracle-l@freelists.org>
> Sent: Tuesday, February 17, 2004 2:42 PM
> Subject: Re: ORA-01650
>
> Actually, more frequent commits can increase the likelihood of
> 1650s. When a rollback segment extends beyond optimal, one of the
> conditions for shrinkage is that two transactions find it over
> extended and request that it shrink to optimal. Here's a scenario
> that caused me problems. We scheduled a massive load over a weekend
> when no one else would be using the system. The load would commit
> every 100k rows. At the 5th iteration, the load would fail with a
> 1650. The commit signals a new transaction, so a new rbs was
> assigned after each commit. Tx1 used RBS1 and extended it. Tx2 used
> RBS2 and extended it, etc. By the time Tx5 came along, RBS1 - RBS4
> had extended and consumed almost all the space in the tablespace.
> When Tx5 needed space, it could not grab any more. It needed to have
> space released from the other RBSs, but there were not other txs
> that could find the overextended rbs and request that they shrink.
>
> In the case presented, it looks like you have many rbss. This could
> be the same problem, if there is not enough other activity. Of
> course, then you risk getting 1555s.
>
> It is important to remember that deletes are not just table level,
> you also have indexes to deal with. If you have enough indexes, you
> could be generating more index undo that table undo.
>
> Quick answer is to increase the size of the rbs tablespace.
>
> Daniel
>
> Lee Lee wrote:
>
> > You could separate your delete into smaller deletes:
> > Delete 1 million rows, commit, delete another million,
> > commit, ...
> >
> > -----Original Message-----
> > From: solbeach@cox.net [mailto:solbeach@cox.net]
> > Sent: Tuesday, February 17, 2004 10:23 AM
> > To: oracle-l@freelists.org
> > Subject: ORA-01650
> >
> > I need someone to clarify what I am seeing.
> >
> > Table contains approximately 6.6 million rows.
> > Each row is no more than 400 bytes.
> > Want to delete approximately 5 million rows.
> >
> > Getting the following error message:
> > ORA-1650: unable to extend rollback segment RBS23 by
> > 1536 in         tablespace RBS
> >
> > The total size of the RBS tablespace is about 6GB!
> > I do not understand why the RBS is "too small" to
> > accommodate this DELETE.
> > What, if anything, can be done other than adding
> > another datafile to the tablespace.
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------