RE: Global temporary tables with No logging
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <walid_alkaakati@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Jun 2009 10:10:15 -0400
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/statement
s_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
PAREXEL 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:
