RE: temp tablespace blow up

Just because no one else mentioned it (that I noticed) I'll point out that having a large number of extents in the temp tablespace is fine and no reason to drop it. The temporary extents are reused whenever needed.  

If you are ever concerned about current usage of the temp tablespace you can look at v$sort_usage.  

Jay Miller
Sr. Oracle DBA
201.369.8355  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lyall Barbour Sent: Tuesday, August 14, 2007 2:18 PM
To: oracle-l_at_freelists.org
Subject: 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;  

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_at_sanfranmail.com 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 Mail.com
<http://www.mail.com/?utm_source=mail_sent_footer&utm_medium=email&utm_t
erm=070621&utm_content=textlink&utm_campaign=we_got_your_name> 
Get a FREE E-mail Account Today - Choose From 100+ Domains

--
http://www.freelists.org/webpage/oracle-l