Re: TRUNCATE TABLE acquire rollback segment?

"Tom" <cmc3232_at_yahoo.com> wrote in message news:Y9EG6.658205$f36.18728321_at_news20.bellglobal.com...
> I always thinking TRUNCATE TABLE is fast because no rollback is generated
> for the operation. Suddenly, I found foolowing in COUCHMAN's DBA
> certifaction book:
>
> "Despite your inability to rollback a table truncation, Oracle does
 acquire
> a rollback segment for the job. Why? Because if you terminate the
 truncate
> table command, or some failure occurs, the rollback segments stores the
> changes made for the duration of the truncate operation to enable crash
> recovery."
>
> I do not quite understand what information is put in the rollback for the
> TRUNCATE operation. Your help is appreciated.
>
> Tom
>
>
>

Of course it will need a rollback segment, but not for the purpose you think, not to rollback the data. TRUNCATE is a DDL transaction, and all DDL transactions should rollback when any error occurs (or the user cancels the operation). The difference with DML transactions is - they are automatically committed
- they need by design the SYSTEM rollback segment in the tablespace SYSTEM

So, yes, Couchman is absolutely correct.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Apr 28 2001 - 15:32:18 CDT