Re: Rollback question

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....

>Jaap.
>
>On 3 Feb 2002 07:49:18 -0800, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>>In article <3C5D43ED.C956D19E_at_ictnet.es>, Artur says...
>>>
>>>Then, Is it impossible to deactivate the rollback function?
>>>
>>>Thanks.
>>>
>>
>>with the exception of a very few commands -- yes. You might believe you don't
>>"need to rollback" but you do.
>>
>>Suppose you were in the middle of a DELETE and the system failed. If we didn't
>>have rollback for it -- you would have a corrupt database, you lose
>>
>>there are some commands that will bypass undo and redo generate, like a direct
>>path insert (insert /*+ append */ into t select ...), create table as select
>>nologging, create index nologging, but in general, all insert/update/delete
>>activity will generate undo.
>>
>>
>>>
>>>En/Na "Jaap W. van Dijk" ha escrit:
>>>
>>>> You can't.
>>>>
>>>> Rollback is not only used for rolling back but also for guaranteeing
>>>> that a query of another user sees the database as it is at the start
>>>> of the query: of all blocks that change during the runtime of the
>>>> query the original data is retrieved from the rollback the updating
>>>> processes have generated.
>>>>
>>>> Jaap.
>>>>
>>>> On Sun, 03 Feb 2002 13:16:05 +0100, Artur Undebarrena
>>>> <aundebarrena_at_ictnet.es> wrote:
>>>>
>>>> >Hello.
>>>> >
>>>> >I need to tell to Oracle database that do not use the rollback SQL
>>>> >command to accelerate the delete command. I use databases in development
>>>> >process and I don't need to rollback the inserts, updates and deletes. I
>>>> >want to accelerate my sql queries and I think if I deactivate the
>>>> >rollback command I'll win in speed.
>>>> >
>>>> >
>>>> >Thank you very much.
>>>> >
>>>> >
>>>> >
>>>
>>
>>--
>>Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>>Expert one on one Oracle, programming techniques and solutions for Oracle.
>>http://www.amazon.com/exec/obidos/ASIN/1861004826/
>>Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp