Re: Rollback question

I almost want to say that it's an undocumented feature that nologging also means no undo. I only know discrete transactions do not generate undo although it generates redo. I finally find in Oracle8i Data Warehousing Guide, Chapter 18
(http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76994/tuningpe.htm#53133) that says:

When a table or index has NOLOGGING set, neither parallel nor serial direct-load INSERT operations generate undo or redo logs.

In all other places of documentation that talks about nologging, there's no mention of no undo.

Question for Tom: The create table statement does not have "nologging". No "alter table t nologging" follows. How can t be nologging, even though insert /*+ append */ is used?

Yong Huang
yong321_at_yahoo.com

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<a3k2kp08q5_at_drn.newsguy.com>...
> In article <3c5d6a5e.18310708_at_news.jaapwvandijk.myweb.nl>, j.w.vandijk_at_hetnet.nl
> says...
> >
> >My understanding is that, unlike the generation of redo information
> >which can often be switched off, undo information is always generated.
> >Or has this changed in Oracle 9i?
> >
>
> no, non-logged operations do not need to generate undo either.
>
> They typically work by performing their duties ABOVE the high water mark or in
> temp segments that will be converted into real ones later. To "undo" them, we
> just reset the high water mark.
>
>
> Consider:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create rollback segment rbs_small tablespace
> system
> 2 storage ( initial 32k next 32k minextents 2 maxextents 2 );
> Rollback segment created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter rollback segment rbs_small online;
> Rollback segment altered.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;
> Table dropped.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects
> where 1=0;
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set transaction use rollback segment
> rbs_small;
> Transaction set.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;
> insert into t select * from all_objects
> *
> ERROR at line 1:
> ORA-01562: failed to extend rollback segment number 7
> ORA-01628: max # extents (2) reached for rollback segment RBS_SMALL
>
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;
> Table dropped.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects
> where 1=0;
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set transaction use rollback segment
> rbs_small;
> Transaction set.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert /*+ APPEND */ into t select * from
> all_objects;
> 17155 rows created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
>
> The first "logged" insert fails due to insufficient RBS space. The second one,
> in the same RBS works....
Received on Sun Feb 03 2002 - 23:17:44 CST