temp tablespace blow up

Mailing List

Home

Forum Home

Oracle List - by freelists.org

Oracle on SUSE Linux - Runing Oracle on SUSE Linux

Oracle database error code ...

www.freelists.org

Subjects

•	ORA 12540: TNS:internal limit restriction exceeded
•	ORA 12838 please : Is possible to append two times to the same table befo
•	ORA 12838 please : Is possible to append two times to the same table before
•	ora 04031
•	ora 12500 on windows
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ora 12500 on windows
•	ORA 01650, one idea
•	ORA 01650
•	ORA 4030
•	ORA 12838 please : Is possible to append two times to thesametable before do
•	ORA 12838 please : Is possible to append two times to thesame table before d
•	ORA 01536
•	ORA 03113 end of file on communication channel
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 00600:
•	ORA 00020: maximum number of processes (%s) exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 3113 while creating a cluster database 9201 RAC on Linux with OCFS
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 01650, one idea
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 2000 Error Using DBMS STATS GATHER SCHEMA STATS
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650
•	ORA 01650
•	ORA 01031
•	ORA 4030
•	ORA 4030
•	ORA 06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
•	ORA 01722 invalid number

temp tablespace blow up

temp tablespace blow up

2007-08-14 - By Lyall Barbour

Back
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@(protected), lyallbarbour@(protected)
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@(protected)
www.proquest.com
www.csa.com

ProQuest...Start here.

From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Alvaro Jose Fernandez
Sent: Tuesday, August 14, 2007 2:56 PM
To: lyallbarbour@(protected)
Cc: oracle-l@(protected)
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@(protected) [mailto:oracle-l-bounce@(protected)]
En nombre de Lyall Barbour
Enviado el: martes, 14 de agosto de 2007 20:18
Para: oracle-l@(protected)
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

<DIV>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.</DIV>
<DIV> </DIV>
<DIV>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?</DIV>
<DIV> </DIV>
<DIV>Lyall </DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff
2px solid">-- -- Original Message -- -- From: "Bobak, Mark" <MARK.BOBAK@(protected)
.PROQUEST.COM> To: alvaro.fernandez@(protected), lyallbarbour@(protected)
 Subject: RE: temp tablespace blow up Date: Tue, 14 Aug 2007 16:46:42
-0400 
<META content="Microsoft Word 12 (filtered medium)" name=Generator>
<DIV class=Section1>
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.<
?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>
</o:p>
<o:p> </o:p>
For a good summary of the different possible types of
temporary tablespaces, see this URL:<o:p></o:p>
<A href="http://www.ixora.com.au/newsletter/2001_06.htm
#temp">http://www.ixora.com.au/newsletter/2001_06.htm#temp</A><o:p></o:p>

<o:p> </o:p>
Yes, it?s dated, but it?s still accurate.<o:p></o:p>
<o:p> </o:p>
-Mark<o:p></o:p>
<o:p> </o:p>
<DIV>
-- 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 <A href="mailto
:mark.bobak@(protected)">mark.bobak@(protected)</A> <A href="http://www
.proquest.com">www.proquest.com</A> <A href="http://www.csa.com">www.csa.com</A> 
ProQuest...Start here. <o:p></o:p
></DIV>
<o:p> </o:p>
<DIV>
<DIV style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df
1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none;
PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce
@(protected)] On Behalf Of Alvaro Jose Fernandez Sent:
Tuesday, August 14, 2007 2:56 PM To: lyallbarbour@(protected) 
Cc: oracle-l@(protected) Subject: RE: temp tablespace blow up
<o:p></o:p></DIV></DIV>
<o:p> </o:p>
Just a dbfile like the others. <o:p></o:p>
<o:p> </o:p>
Just curious, which Oracle release you run? 8i? <o:p><
/o:p>
<o:p> </o:p>
<o:p> </o:p>
Alvaro<o:p></o:p>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center>
<HR align=center width="100%" SIZE=2>
</DIV>
De: oracle-l-bounce@(protected) [mailto:oracle-l
-bounce@(protected)] En nombre de Lyall Barbour Enviado el:
martes, 14 de agosto de 2007 20:18 Para: oracle-l@(protected) Asunto: Re: temp tablespace blow up<o:p></o:p></DIV>
<o:p> </o:p>
<DIV>
just ran an update that had some stupid huge
joins and couldn't allocate anymore extents because the 4gb of datafiles&nbsp
;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:<o:p></o:p></DIV>
<DIV>
CREATE TEMPORARY TABLESPACE blah TEMPFILE '
/d01/oradata/PROD/blah.dbf' size 1000M;<o:p></o:p></DIV>
<DIV>
 <o:p></o:p></DIV></DIV><
/BLOCKQUOTE> 

--
<div>We've Got Your Name at <a href="http://www.mail.com/?utm_source=mail_sent
_footer&utm_medium=email&utm_term=070621&utm_content=textlink&utm_campaign=we
_got_your_name"target="_blank">Mail.com</a> 
Get a FREE E-mail Account Today - Choose From 100+ Domains</div>