RE: Global temporary tables with No logging

  • From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
  • To: <Richard.Goulet@xxxxxxxxxxx>, <walid_alkaakati@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 8 Jun 2009 16:24:46 +0100

If this application is a PeopleSoft, then you can only convert temporary
working storage tables to GTTs if the application is not restartable.
Otherwise, it expects to find data in the tables from when the process
crashed, so that it can continue from the last commit point.
 

regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
<http://www.psftdba.com/> 
DBA Blogs: PeopleSoft: http://blog.psftdba.com <http://blog.psftdba.com/> ,
Oracle: http://blog.go-faster.co.uk <http://blog.go-faster.co.uk/> 
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba 

 


  _____  

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_70
02.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: