If using delete, nologging does not affect redo either. Unless you used CTAS. ----- Original Message ----- From: "Daniel W. Fink" <Daniel.Fink@xxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Wednesday, February 18, 2004 6:29 AM 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@xxxxxxx> > > To: <oracle-l@xxxxxxxxxxxxx> > > 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@xxxxxxx [mailto:solbeach@xxxxxxx] > > > Sent: Tuesday, February 17, 2004 10:23 AM > > > To: oracle-l@xxxxxxxxxxxxx > > > 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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------