Re: switch redo logs off for one table

Actually it is great!

Oracle fools us again :-)

Jim Kennedy wrote:

> 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 Wed Dec 25 2002 - 03:27:05 CST