Re: Temporary table use and the redo log

From: Robert Klemme <>
Date: Mon, 07 Sep 2009 21:22:29 +0200
Message-ID: <>

On 07.09.2009 19:57, Mladen Gogala wrote:
> On Mon, 07 Sep 2009 09:23:31 -0700, wrote:
>> Hi
>>   I'm using a global (session) temporary table to break up a section
>> of a stored procedure that is needed in a number of places. Always for
>> use in a select.
>>   If the database crashes, gets rolled back, anything, I don't care
>> about the contents of that
>> table. Especially -I don't want redo logs for it-. Is there any way I
>> can accomplish this?
>> From what I can see so far I should look into: - unrecoverable
>> - insert /*+APPEND */ into temp_table select... - possibly using a table
>> variable
>> Nologging is on by default for a tempory table but you still get some
>> logging
>> Am I on the right track?
>> thanks

> The storage for temporary tables is allocated entirely from the temporary
> tablespace. Blocks in a temporary tablespace are not proteced by redo so
> nologging is on by default as you say. Even more than that, there is no
> "current version" and "read consistent" version of the temporary blocks.
> Temporary blocks are written from PGA to temporary tablespace in a way
> very similar to what /*+ APPEND */ hint does. In other words, that hint is
> for normal insert and has no visible effect on inserting into global
> temporary tables. To verify that, you can run it with the event 10046,
> level 12 and see that there are no visible differences in the trace file.

But there is a bit of redo because of the undo:

(Hopefully that link works...)

Kind regards


remember.guy do |as, often| as.you_can - without end
Received on Mon Sep 07 2009 - 14:22:29 CDT

Original text of this message