Re: Rollback extents

"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0206271902.6a3d8692_at_posting.google.com...
> "Steve Ashmore" <sashmore_at_neonramp.com> wrote in message
news:<uhmv3r9c783o00_at_corp.supernews.com>...
> > That is what I get for working on this at 5AM.
> >
> > Nologging is of course for used to suspend redo generation.
> >
> > Interesting though. If you do a direct load insert
> > you can still issue a rollback and at least some
> > rollback information is generated. I was playing with this today.
> >
>
> DMLs all generate redo, because they can all be rolled back unless you
> set autocommit to true. I have yet to see an exception.

This is true, but how much rollback is a different question.

SQL> drop table nl_objects;

Table dropped.

SQL> create table nl_objects
  2 as select * from all_objects;

Table created.

SQL> insert into nl_objects select * from nl_objects;

29085 rows created.

SQL> / 58170 rows created.

SQL> / 116340 rows created.

SQL> commit;

Commit complete.

SQL> insert into nl_objects select * from nl_objects;

232680 rows created.

SQL> select used_urec,used_ublk
  2* from v$transaction
SQL> /  USED_UREC USED_UBLK
---------- ----------

      5872 105

SQL> rollback;

Rollback complete.

SQL> insert /*+ append */ into nl_objects select * from nl_objects;

232680 rows created.

SQL> select used_urec,used_ublk
  2 from v$transaction;

 USED_UREC USED_UBLK
---------- ----------

         1 1

SQL> rollback;

Rollback complete.

SQL> So my normal insert into uses 105 blocks of undo space. The direct load insert uses 1.

As for exactly what is recorded in undo/rollback in the case of direct load then I think you are into Jonathan territory.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************