Re: Temporary table use and the redo log

Thanks for the reply. I'm probably missing something based on limited understanding.

I can see on my Oracle database there are 3 redo log files, from some reading it seems these are populated in turn and these later populate archive files. Its those archive files I'm looking at.

I created a script which using a cursor and a loop populated a temporary table using 1 million inserts. I can see the archive log files increasing as this happens in:

D:\oracle\product\10.1.0\flash_recovery_area\<oracle name>\ARCHIVELOG \2009_09_07

The temporary table looks something like this:

CREATE GLOBAL TEMPORARY TABLE TP_TEMP
(

        somecolumn INT,

) ON COMMIT PRESERVE ROWS; We have perhaps 10 stored procedures that create the temp table and then call 2 stored procedures to populate it so that they can then do whatever they need to with the output. Once the calling stored procedures are finished then the contents of the temporary table can be discarded.

If what your saying is true then I didn't think those archive files would be increasing?

Also using the /*+ APPEND */ hint did seem to make a difference, those files weren't increasing, although in retrospect I'm not not convinced it was a valid test, it did about 1 insert of 10,000 rows as I could only
call it once before I got some kind of error. I'll try it again tomorrow

thanks for your replies, I appreciate it Received on Mon Sep 07 2009 - 14:57:25 CDT