RE: Global temporary tables with No logging

  • From: Michael Rosenblum <mrosenblum@xxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 8 Jun 2009 12:07:55 -0400

Unfortunately, operations against temporary tables DO have an impact on the 
volume of generated logs – at least as far as I understand.

The problem is that temporary tables do generate rollback – and the rollback is 
logged. So, temporary tables will generate some redo.

Just to summarize the rule of thumb (quoting Tom Kyte's "Expert Oracle Database 
Architecture" - p.322 If you don't have it in paper, here is a link: 
http://books.google.com/books?id=TmPoYfpeJAUC&printsec=frontcover#PPA322,M1 ):

 *   INSERT - little to no rollback/redo activity
 *   DELETE - the same amount of redo as normal table
 *   UPDATE - about half the redo of an UPDATE of a normal table.
Regards,
Michael Rosenblum
Dulcian Inc
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Goulet, Richard
Sent: Monday, June 08, 2009 3:10 PM
To: walid_alkaakati@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Global temporary tables with No logging
Importance: High

If these are global temp tables then their are not the cause of your increased 
archive log activity.  Global temp tables have a default of the temp tablespace 
and are never logged.  The reason is that once a session using a global temp 
table ends the table drops all of it's data & storage.  Transaction 
preservation of global temps is per their definition.  Refer to : 
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2095331.
  It's fro 11g, but still applicable to 10g.

You can specify nologging with the "alter table" command, but that can be 
overridden if the database has supplemental logging turned on as in there 
exists a physical standby.

The biggest thing is to check with the application developers of provider.  
Some apps like PeopleSoft create "temporary" tables that are there to pass data 
between sessions in a batch job.  If that's so, your stuck with them.  If not 
consider rebuilding them as global temps.


Dick Goulet
Senior Oracle DBA
PARE  XEL International



________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of walid alkaakati
Sent: Monday, June 08, 2009 8:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Global temporary tables with No logging



Hallo list,

i have an application that uses about 60 Temporary tables, the problem is that 
each day we i have more than 500 Mb as an increase in Archive logs.

How can i specify No Logging option for these Temporary tables ?  when i try i 
get a syntax error, also i have read in one blog that Global temporary tables 
do Not Genrate Redo, so can some one please clarify the truth ?


Thank you all !



Other related posts: