| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-01650, one idea
Perhaps I misunderstood. My point was that when you do valid NOLOGGING =
operations, no redo OR undo will be logged for the blocks being loaded. =
The only redo/undo logging which will take place is the extent =
invalidation records and any data dictionary updates. The extent =
invalidation records are required to mark the loaded blocks as corrupted =
in case recovery takes place, and the data dictionary updates are =
required in case the transaction rolls back.
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and =
a sense of humor was provided to console him for what he is." --Horace =
Walpole
-----Original Message-----
From: Tanel P=F5der [mailto:tanel.poder.003_at_mail.ee]=20
Sent: Wednesday, February 18, 2004 1:37 PM
To: oracle-l_at_freelists.org
Subject: Re: ORA-01650, one idea
Hi!
I think Dan is correct here conceptually, since any record that gets =
written
into undo segment, will always be backed up by redo. How else could =
Oracle's
cache layer, which does the logging, distinguish which blocks or parts =
of
blocks of an undo segment should be logged or not.
When you're doing direct path inserts, then just minimum undo =
information
about transaction start is generated, no undo for block formatting nor
filling with rows. And HWM is increased only when commit occurs, not =
before,
so I think even that doesn't require any undo, since we are committing
anyway.
Tanel.
> Not true. NOLOGGING, when it applies, can disable undo logging as =
well
> as redo logging.
>
> Consider though, that it can only be implemented w/ Direct-Path =
inserts
> (insert /*+ append */ and SQL*Loader) and some DDL operations (index
> rebuilds, etc).
>
> -Mark
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Imagination was given to man to compensate him for what he is not, =
and
> a sense of humor was provided to console him for what he is." =
--Horace
> Walpole
>
>
> -----Original Message-----
> From: Daniel W. Fink [mailto:Daniel.Fink_at_Sun.COM]=3D20
> Sent: Tuesday, February 17, 2004 5:29 PM
> To: oracle-l_at_freelists.org
> Subject: Re: ORA-01650, one idea
>
>
> 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_at_Sun.COM>
> > To: <oracle-l_at_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_at_cox.net [mailto:solbeach_at_cox.net]
> > > Sent: Tuesday, February 17, 2004 10:23 AM
> > > To: oracle-l_at_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_at_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_at_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_at_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_at_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
> -----------------------------------------------------------------
>
-- 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_at_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 -----------------------------------------------------------------Received on Wed Feb 18 2004 - 13:16:40 CST
 |
 |