RE: temp tablespace blow up

OK, that all makes sense, now i have another question. What should the pctincrease be for a Temporary tablespace? In database creation scripts i've seen from Oracle, Rollback, Temp and User tablespaces are all set to 0. If the temp tablespace is going to grow quickly, it's because of some (or somessss) query that need to run through a lot of rows, right? Wouldn't you want as few extents in the temp tablespace as possible if this is going to happy? Why not make the TEMP tablespace one big extent? Lyall

• Original Message ----- From: "Bobak, Mark" To: alvaro.fernandez_at_sivsa.com, lyallbarbour_at_sanfranmail.com Subject: RE: temp tablespace blow up Date: Tue, 14 Aug 2007 16:46:42 -0400

A tempfile is not just another datafile. For one thing, it’s initially sparse. For another, a tempfile tablespace does not need to be backed up, and at recovery time, is simply (and quickly) recreated.

For a good summary of the different possible types of temporary tablespaces, see this URL:

http://www.ixora.com.au/newsletter/2001_06.htm#temp

Yes, it’s dated, but it’s still accurate.

-Mark

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alvaro Jose Fernandez Sent: Tuesday, August 14, 2007 2:56 PM
To: lyallbarbour_at_sanfranmail.com
Cc: oracle-l_at_freelists.org
Subject: RE: temp tablespace blow up

Just a dbfile like the others.

Just curious, which Oracle release you run? 8i?

Alvaro

De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Lyall Barbour
Enviado el: martes, 14 de agosto de 2007 20:18 Para: oracle-l_at_freelists.org
Asunto: Re: temp tablespace blow up

just ran an update that had some stupid huge joins and couldn't allocate anymore extents because the 4gb of datafiles were too small. So my update errored. then, no problem still, just that my temp tablespace had an enormous amount of extents. So, i dropped it when users were off and recreated it as TEMPORARY. I've been reading about temporary tablespaces:

CREATE TEMPORARY TABLESPACE blah TEMPFILE '/d01/oradata/PROD/blah.dbf' size 1000M;

--

We've Got Your Name at http://www.mail.com ! Get a FREE E-mail Account Today - Choose From 100+ Domains

--

http://www.freelists.org/webpage/oracle-l Received on Tue Aug 14 2007 - 16:28:40 CDT