Re: Disable rollback segments?

chamblej_at_author.gsfc.nasa.gov (JEFF CHAMBLEE (512)) writes:
|>
|> Hey folks,
|>
|> Is there any way to temporarily turn off use of a rollback segment?
|> Often I need to delete everything from a table. In the past I've
|> tried 2 approaches:
|>
|> 1. I say,
|> DELETE FROM table;
|> It takes a long time because it's writing the changes to a rollback
|> segment. Then it blows out that RS. So I say,
|> SET TRANSACTION USE ROLLBACK SEGMENT x;
|> DELETE FROM table;
|> It takes a long time and it still blows out the new RS.
|>
|> 2. I say,
|> DROP TABLE table;
|> Then I have to re-create it, re-grant access to it, etc. It's a bit of
|> a pain.
|>
|> I've looked at the following commands, but I haven't found anything
|> helpful.
|> SET TRANSACTION
|> ALTER SESSION
|> ALTER SYSTEM
|> DROP TABLE
To lessen the impact on the rollback segments, you can set up a loop that basically says
delete from table where rownum <= 1000; /* or your favorite number */ commit;

And keep looping until there's 0 rows deleted.

Oracle7 introduces a TRUNCATE table statement which will delete all the rows from a table without needing much rollback segment space.

Hope this helps.
Roderick Manalac
Oracle Corporation

        [Posting again for the heck of it] Received on Sat Aug 14 1993 - 10:31:25 CEST