 | | | temp tablespace blow up | temp tablespace blow up 2007-08-14 - By Lyall Barbour
Back
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; apparently this creates the idea
of a file on the OS. Maybe my question is really what is a TEMPFILE?
-- -- Original Message -- --
From: "Don Seiler"
To: lyallbarbour@(protected)
Subject: Re: temp tablespace blow up
Date: Tue, 14 Aug 2007 09:43:37 -0500
A temporary tablespace is what you want for TEMP.
I'm curious as to how you think you blew it up, and even more curious
as to how Oracle let you drop it. What version of Oracle are you
using?
Don.
On 8/14/07, Lyall Barbour wrote:
> Hi all,
> So, i blew up _the_ temp tablespace the other day with an update
query
> *pats himself on the back*. I found some down time and dropped it,
looking
> up on the web how to recreate it, i found CREATE TEMPORARY
TABLESPACE and
> created it as "temporary" not realizing what i was doing (just
hadn't done
> it in awhile).
> Now, night reports and such take 4 hrs instead of 4 mins to run.
> Now that i've found the basic syntax to create a tablespace again,
my only
> real question is if someone can explain about TEMPORARY tablespaces
and what
> good they'd be for? Are they good for the "temp" tablespace for
queries
> cause there's only two reasons i found for the huge amount of time
> difference for the night process 1) tablespace used to be regular 4
gig
> tablespace, now it's "TEMPORARY" 2) i created the extents wrong and
instead
> of being 767 extents it was 37000.
>
> What is a TEMPORARY TABLESPACE?
>
> Lyall
> --
> We've Got Your Name at Mail.com
> Get a FREE E-mail Account Today - Choose From 100+ Domains
--
Don Seiler
oracle: http://ora.seiler.us
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l
--
We've Got Your Name at http://www.mail.com !
Get a FREE E-mail Account Today - Choose From 100+ Domains
<DIV>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:</DIV>
<DIV>CREATE TEMPORARY TABLESPACE blah TEMPFILE '/d01/oradata/PROD/blah.dbf'
size 1000M;</DIV>
<DIV> </DIV>
<DIV>apparently this creates the idea of a file on the OS. Maybe my
question is really what is a TEMPFILE?<BR><BR></DIV>
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff
2px solid">-- -- Original Message -- --<BR>From: "Don Seiler" <DON@(protected)>
<BR>To: lyallbarbour@(protected)<BR>Subject: Re: temp tablespace blow up<BR
>Date: Tue, 14 Aug 2007 09:43:37 -0500<BR><BR><BR>A temporary tablespace is what
you want for TEMP.<BR><BR>I'm curious as to how you think you blew it up, and
even more curious<BR>as to how Oracle let you drop it. What version of Oracle
are you<BR>using?<BR><BR>Don.<BR><BR>On 8/14/07, Lyall Barbour <LYALLBARBOUR
@(protected)>wrote:<BR>> Hi all,<BR>> So, i blew up _the_ temp
tablespace the other day with an update query<BR>> *pats himself on the back
*. I found some down time and dropped it, looking<BR>> up on the web how to
recreate it, i found CREATE TEMPORARY TABLESPACE and<BR>> created it as
"temporary" not realizing what i was doing (just hadn't done<BR>> it in
awhile).<BR>> Now, night reports and such take 4 hrs instead of 4 mins to
run.<BR>> Now that i've found the basic syntax to create a tablespace again,
my only<BR>> real question is if someone can explain about TEMPORARY
tablespaces and what<BR>> good they'd be for? Are they good for the "temp"
tablespace for queries<BR>> cause there's only two reasons i found for the
huge amount of time<BR>> difference for the night process 1) tablespace used
to be regular 4 gig<BR>> tablespace, now it's "TEMPORARY" 2) i created the
extents wrong and instead<BR>> of being 767 extents it was 37000.<BR>><BR
>> What is a TEMPORARY TABLESPACE?<BR>><BR>> Lyall<BR>> --<BR>>
We've Got Your Name at Mail.com<BR>> Get a FREE E-mail Account Today -
Choose From 100+ Domains<BR><BR><BR>--<BR>Don Seiler<BR>oracle: http://ora
.seiler.us<BR>ultimate: http://www.mufc.us<BR>--<BR>http://www.freelists.org
/webpage/oracle-l<BR></BLOCKQUOTE><BR>
--
<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><br>
Get a <b>FREE</b> E-mail Account Today - Choose From 100+ Domains</div>
|
|
|