Re: delete w/o rollback segs???

<caramel98_at_my-deja.com> wrote in message news:84sk9a$sgm$1_at_nnrp1.deja.com...
> Hi,
>
> You ca use TRUNCATE TABLE table_name.
> This will delete all rows.
>
> You can't delete only some rows without using
> rollback segments. However, you can choose the
> rollback to use with the SET TRANSACTION USE
> ROLLBACK SEGMENT segment_name (or some thing like
> that)

Not true.
To truncate a table, Oracle MUST use rollback segments to record the rollback info for the change of data dictionary.

In the following example, Oracle use rollback segment #2, #3, #4 to accomplish a "truncate table".
From v$sysstat, it also says Oracle issues commit 3 (18-15) times.

SQL> select usn, writes, gets from v$rollstat;

      USN WRITES GETS
--------- --------- ---------

        0      2940        34
        2       268         9
        3        54         7
        4        54         7
        5        54         7
        6        54         7
        7        54         7
        8        54         7

8 rows selected.

SQL> select name, value from v$sysstat where name='commit cleanouts';

NAME                                                                 VALUE
---------------------------------------------------------------- ---------
commit cleanouts                                                        15

SQL> truncate table t;

Table truncated.

SQL> select usn, writes, gets from v$rollstat;

      USN WRITES GETS
--------- --------- ---------

        0      2940        35
        2       466        14
        3       912        15
        4       108        12
        5        54         8
        6        54         9
        7        54         8
        8        54         8

8 rows selected.

SQL> select name, value from v$sysstat where name='commit cleanouts';

NAME                                                                 VALUE
---------------------------------------------------------------- ---------
commit cleanouts                                                        18