Re: Temporary table use and the redo log

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 07 Sep 2009 22:45:36 +0200
Message-ID: <7gl9npF2pirlkU1_at_mid.individual.net>

On 07.09.2009 21:57, codefragment_at_googlemail.com wrote:

>> The storage for temporary tables is allocated entirely from the temporary
>> tablespace.

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

I suggest you go to http://tahti.oracle.com and read the Concepts manual. Then there's also documentation about administration and backups in particular.

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

No. Redo is created and never deleted. You have these options to limit your archive log volume

1. switch off archive log mode
2. set up RMAN so that you do regularly backups and purge old backups

2b. set up RMAN so that it only deletes old archive logs which is a bit pointless without backups (i.e. then you'll better pick option 1).

If you do 1 you can basically forget backing up your database. Whether this is OK for you, only you can decide. Often test databases are excluded from backup schemes but if your DB is a production system, chances are that you rather want option 2.

Cheers

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Received on Mon Sep 07 2009 - 15:45:36 CDT