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-15       - By Alvaro Jose Fernandez

 Back
Lyall,



Extents from temporary sort segments are allocated to one sessions at a time
only, and are not deallocated until that session frees them. They are not
shared between sessions.



I think it would be reasonable to set extent size = 1 Mbyte , and set
PCTINCREASE=0 and INITIAL=NEXT (this way freed extents will match the next
request extent size, and no need for SMON to consolidate freed extents, I think
). Intermintently, run a



select sid, serial#, vs.osuser, extents, contents, blocks from v$session vs,

   v$sort_usage su  where vs.saddr = su.session_addr ;



to learn session's usage.



In 8.1 there are much more options (EXTENT MANAGEMENT LOCAL/uniform extent size
).



?You cannot upgrade?



regards

--



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


<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="http:
//www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=iso-8859 (See http://iso-8859.ora-code.com)-1">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
  {margin:0cm;
  margin-bottom:.0001pt;
  font-size:12.0pt;
  font-family:"Times New Roman";}
a:link, span.MsoHyperlink
  {color:blue;
  text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
  {color:blue;
  text-decoration:underline;}
p
  {mso-margin-top-alt:auto;
  margin-right:0cm;
  mso-margin-bottom-alt:auto;
  margin-left:0cm;
  font-size:12.0pt;
  font-family:"Times New Roman";}
address
  {margin:0cm;
  margin-bottom:.0001pt;
  font-size:12.0pt;
  font-family:"Times New Roman";
  font-style:italic;}
pre
  {margin:0cm;
  margin-bottom:.0001pt;
  font-size:10.0pt;
  font-family:"Courier New";}
span.EstiloCorreo17
  {mso-style-type:personal-reply;
  font-family:Arial;
  color:navy;}
@(protected) Section1
  {size:595.3pt 841.9pt;
  margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
  {page:Section1;}
-->
</style>

</head>

<body lang=ES link=blue vlink=blue>

<div class=Section1>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Lyall,<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Extents from temporary sort segments are
allocated to one sessions at a time only, and are not deallocated until that
session frees them. They are not shared between sessions.<o:p></o:p></span><
/font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>I think it would be reasonable to set
extent
size = 1 Mbyte , and set PCTINCREASE=0 and INITIAL=NEXT (this way freed extents
will match the next request extent size, and no need for SMON to consolidate
freed extents, I think). Intermintently, run a <o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>select sid, serial#, vs.osuser, extents,
contents, blocks from v$session vs, <o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>&nbsp;&nbsp;&nbsp; v$sort_usage su
&nbsp;where vs.saddr = su.session_addr ;<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>to learn session&#8217;s usage.<o:p></o:p>
</span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>In 8.1 there are much more options (EXTENT
MANAGEMENT LOCAL/uniform extent size).<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>?You cannot upgrade?<o:p></o:p></span><
/font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>regards<o:p></o:p></span></font></p>

<div>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>?--<o:p></o:p></span></font></p>

<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p>&nbsp;</o:p></span></font></p>

</div>

<div>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>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?&nbsp;
Wouldn't you want as few extents in the temp tablespace as possible if this is
going to happy?&nbsp; Why not make the TEMP tablespace one big extent?<o:p></o
:p></span></font></p>

</div>

<div>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>&nbsp;<o:p></o:p></span></font></p>

</div>

<div>

<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'>Lyall<o:p></o:p></span></font></p>

</div>

</div>

</body>

</html>