Re: Delete command and commit

In article <4a1u6b$hvo_at_pong.lasc.lockheed.com>, g596101_at_lg3vm.lasc.lockheed.com says...
>You need to revisit your rollback segments to keep this from happening in
>the future. You should either make them bigger or make sure that they
>were created with the OPTIMAL parameter set.
>
>But if you just want to delete the 100,000 rows from the table and
>that is all the rows in the table then the eaiset way would be to just
>drop the table and re-create it.
>
>From: Mika Niiranen <mika.niiranen_at_ttgroup.fi>
>Subject: Delete command and commit
>Hi,
>
>I have a table with 100 000 rows and I have tried to delete all the
>rows, but rollback segment becomes full and delete command fails.
>
>I tried to use Oracle's autocommit feature (set autocommit immediate),
>but same thing happens...
>
>Is there easy way to delete all the rows?

If you want to delete _all_ rows of the table, the TRUNCATE command is just what you need.
Its a DDL command that can't be rolled back and hence doesn't use the rollback segments. Moreover it not only deletes all rows of your table, it reduces the table to its first extent and makes the other extents available to the DBMS. Its far better than DROPping and reCREATing the table because all indexes,constraints,triggers,grants on the table are preserved.

Hope this helps

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /