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
 
>Thanks in advance...
 

>===============================================================================
>Jeff Chamblee "I am but mad north-north-west. When the wind
>Computer Sciences Corp. is southerly, I know a hawk from a handsaw."
>Laurel, MD 20707 USA --Hamlet

Oracle7 supports a TRUNCATE TABLE command that does just what you want. You can even tell it to save the space that the table used to occupy. Be careful though, for TRUNCATE is a DDL statement and can not be rolled back.

Walker White Received on Tue Aug 17 1993 - 03:19:58 CEST