 | | | temp tablespace blow up | temp tablespace blow up 2007-08-14 - By Bobak, Mark
Back
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) <mailto:mark.bobak@(protected)>
www.proquest.com <http://www.proquest.com>
www.csa.com <http://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;
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft
-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn
:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office
:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid
:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3
-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="
#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss=
"urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft
-com:office:component:spreadsheet" xmlns:oa="urn:schemas-microsoft-com:office
:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http:/
/schemas.xmlsoap.org/soap/envelope/" xmlns:D="DAV:" xmlns:x2="http://schemas
.microsoft.com/office/excel/2003/xml" xmlns:ois="http://schemas.microsoft.com
/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap
/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http:/
/schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com
/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sps="http:/
/schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001
/XMLSchema-instance" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile"
xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:mver=
"http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http:/
/schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas
.openxmlformats.org/package/2006/relationships" xmlns:ex12t="http://schemas
.microsoft.com/exchange/services/2006/types" xmlns="http://www.w3.org/TR/REC
-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@(protected)
{font-family:Calibri;
panose-1 (See http://ose-1.ora-code.com):2 15 5 2 2 2 4 3 2 4;}
@(protected)
{font-family:Tahoma;
panose-1 (See http://ose-1.ora-code.com):2 11 6 4 3 5 4 4 2 4;}
@(protected)
{font-family:Consolas;
panose-1 (See http://ose-1.ora-code.com):2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
address
{mso-style-priority:99;
mso-style-link:"HTML Address Char";
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
font-style:italic;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLAddressChar
{mso-style-name:"HTML Address Char";
mso-style-priority:99;
mso-style-link:"HTML Address";
font-family:"Calibri","sans-serif";
font-style:italic;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Arial","sans-serif";
color:navy;}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@(protected) Section1
{size:595.3pt 841.9pt;
margin:70.85pt 85.05pt 70.85pt 85.05pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'>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.<o:p></o:p><
/span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'>For a good summary of the different possible types of temporary
tablespaces, see this URL:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><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></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'>Yes, it’s dated, but it’s still accurate.<o:p></o:p>
</span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'>-Mark<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<p class=MsoNormal><b><span style='font-size:7.5pt;font-family:"Arial","sans
-serif";
color:#1F497D'>--<br>
Mark J. Bobak</span></b><span style='font-size:11.0pt;font-family:"Calibri",
"sans-serif";
color:#1F497D'><br>
</span><b><span style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#1F497D'>Senior Database Administrator, System & Product Technologies
</span></b><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><br>
</span><span style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#1F497D'>ProQuest</span><span style='font-size:11.0pt;font-family:
"Calibri","sans-serif";
color:#1F497D'><br>
</span><span style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#1F497D'>789 E. Eisenhower, Parkway, P.O. Box 1346</span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><br>
</span><span style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#1F497D'>Ann Arbor MI 48106-1346</span><span style='font-size:11.0pt;
font-family:"Calibri","sans-serif";color:#1F497D'><br>
</span><span style='font-size:7.5pt;font-family:"Arial","sans-serif";
color:#1F497D'>+1.734.997.4059 or +1.800.521.0600 x 4059</span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><br>
<a href="mailto:mark.bobak@(protected)"><span style='font-size:7.5pt;
font-family:"Arial","sans-serif"'>mark.bobak@(protected)</span></a><br>
<a href="http://www.proquest.com"><span style='font-size:7.5pt;font-family:
"Arial","sans-serif"'>www.proquest.com</span></a><br>
<a href="http://www.csa.com"><span style='font-size:7.5pt;font-family:"Arial",
"sans-serif"'>www.csa.com</span></a><br>
<br>
</span><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:#1F497D'>ProQuest...</span></b><span style='font-size:10.0pt;font-family:
"Arial","sans-serif";color:#1F497D'>Start here.</span><span style='font-size:
11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> </span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p>
</o:p></span></p>
</div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans
-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in
0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans
-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <b>On
Behalf Of </b>Alvaro Jose Fernandez<br>
<b>Sent:</b> Tuesday, August 14, 2007 2:56 PM<br>
<b>To:</b> lyallbarbour@(protected)<br>
<b>Cc:</b> oracle-l@(protected)<br>
<b>Subject:</b> RE: temp tablespace blow up<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'>Just a dbfile like the others. <o:p></o:p></span></p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'>Just curious, which Oracle release you run? 8i? <o:p></o:p></span><
/p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=ES style='font-size:10.0pt;font-family:"Arial",
"sans-serif";
color:navy'>Alvaro<o:p></o:p></span></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><span lang=ES>
<hr size=2 width="100%" align=center>
</span></div>
<p class=MsoNormal><b><span lang=ES style='font-size:10.0pt;font-family:"Tahoma
","sans-serif"'>De:</span></b><span
lang=ES style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <b>En
nombre
de </b>Lyall Barbour<br>
<b>Enviado el:</b> martes, 14 de agosto de 2007 20:18<br>
<b>Para:</b> oracle-l@(protected)<br>
<b>Asunto:</b> Re: temp tablespace blow up</span><span lang=ES><o:p></o:p><
/span></p>
</div>
<p class=MsoNormal><span lang=ES><o:p> </o:p></span></p>
<div>
<p class=MsoNormal><span lang=ES>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:<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=ES>CREATE TEMPORARY TABLESPACE blah TEMPFILE
'/d01/oradata/PROD/blah.dbf' size 1000M;<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=ES> <o:p></o:p></span></p>
</div>
</div>
</body>
</html>
|
|
|