Re: Delete command and commit

jb2_at_qdot.qld.gov.au (John Blackburn) writes:
|> Mika Niiranen <mika.niiranen_at_ttgroup.fi> wrote:
|>
|> : 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?
 

|> : -= Mika =-
 

|> : ---------------------------------387293229386--
|>
|> try:
|>
|> TRUNCATE TABLE <tablename>
|>

You didn't tell us what version of Oracle you are on. The TRUNCATE command is nifty but only available in Oracle7. One way to get around it in Oracle V6 was to write something like

delete from foo
where rownum < 10000;
commit;

Repeat statements until table is empty. Just creating a new table is another option though you would also have to recreate indexes, grants, etc.

Hope this helps.
Roderick Received on Wed Dec 13 1995 - 00:00:00 CET