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 -----------------------------------------------------------------