Thanks. Good demonstration.
Jim
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:L7gM9.6297$jM5.17207_at_newsfeeds.bigpond.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:XL3M9.5895$jM5.16983_at_newsfeeds.bigpond.com...
> > The answer is no.
> >
> > If a transaction generates redo (and every normal insert, delete or
update
> > always does, regardless of your setting for NOLOGGING), then that redo
> will
> > be written to the online logs, and online logs get archived, so your
> > transactions are in the archived redo logs.
> >
> > As someone else has mentioned, you might find that 'create global
> temporary
> > table BLAH (col1 number, col2 char(5)) etc' fits the bill, as
transactions
> > in global temporary tables are never logged... largely because the data
> > inserted into such a table is only visible to the session that put them
> > there.
>
> Hi Howard, Jim and all,
>
> Suggestions that transactions to temporary tables are never logged is not
> quite correct and is a little deceptive. It's kinda right but not quite.
In
> actual fact changes to temporary tables can produce a significant amount
of
> redo.
>
> Let me give a simple demo then I'll try to explain.
>
> SQL> create global temporary table bowie_test on commit preserve rows as
> select * from dba_source;
>
> Table created.
>
> SQL> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
> ---------- ---------- ---------- ---------- ---------- --- ---------------
-
> FIRST_CHANGE# FIRST_TIM
> ------------- ---------
> 1 1 64 1048576 1 NO INACTIVE
> 19490665 19/DEC/02
>
> 4 1 65 1048576 1 NO INACTIVE
> 19490834 19/DEC/02
>
> 5 1 66 1048576 1 NO CURRENT
> 19490837 19/DEC/02
>
>
> SQL> update bowie_test
> 2 set text = 'BOWIE';
>
> 145115 rows updated.
>
> SQL> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
> ---------- ---------- ---------- ---------- ---------- --- ---------------
-
> FIRST_CHANGE# FIRST_TIM
> ------------- ---------
> 1 1 139 1048576 1 NO ACTIVE
> 19504906 19/DEC/02
>
> 4 1 140 1048576 1 NO CURRENT
> 19505046 19/DEC/02
>
> 5 1 138 1048576 1 NO INACTIVE
> 19504746 19/DEC/02
>
> I am the only user on my little baby DB and yet changes to just a simple
> little temporary table has produced about 74M of redo (the small redo logs
> are there purely for effect ;)
>
> The key point is this. Changes to temporary tables produces *undo* as I
> might decide to rollback the changes above. Although the changes to the
> temporary table itself are not logged, the changes to the undo segments
> *are* logged. This is because we may need to recover a specific undo
> datafile and need to determine what is what. Therefore changes to
temporary
> tables can produce a significant amount of redo.
>
> An important point that I thought needed clarifying.
>
> Cheers
>
> Richard
>
>
Received on Thu Dec 19 2002 - 07:51:53 CST
