RE: How do you calcuate the temp space needed for view?

Hi Rafiq,

My database version is 7.3.3.6.0, so I cannot set the maxextents unlimited. I added another datafile and increase initial extent and next extent much larger for this tablespace. My user still encountered the error. I would like to know how much space is needed for selecting the view. Any advice ? TIA.

Regds,
New Bee

		-----Original Message-----
		From:	M Rafiq [mailto:rafiq9857_at_hotmail.com]
		Sent:	Friday, February 21, 2003 11:04 AM
		To:	Multiple recipients of list ORACLE-L
		Subject:	Re: How do you calcuate the temp space needed for view?

		Your database having db_block_size 2K so max extents are 121. You may alter 
		your temp tablespace by

		alter tablespace temp default storage(maxextents unlimited); then
		alter tablespace temp coalesce;

		and try your query. If space is not sufficient in temp ts then message will 
		come with ORA-1652. In that case you have to increase size of temp 
		tablespace by resizing extisting file or adding another datafile.


		HTH,

		Regards
		Rafiq







		Reply-To: ORACLE-L_at_fatcity.com
		To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
		Date: Thu, 20 Feb 2003 17:08:49 -0800

		Hi Gurus,

		I issue the following command select count(*) from view1 and encounter the 
		following error "ORA-01630: max # extents (121) reached in temp segment in 
		tablespace TEMP".  I think I need to increase the tablespace TEMP  but how 
		do I calculate the temporary space needed ?

		Assuming view1 is "select * from table1, table2 where 
		table1.Col1=table2.Col1"
		Is the temporary space needed = (table 1 row-length * table 1 total number 
		of rows ) * (table 2 row-length * table 2 total number of rows ) ?

		Any advice ? Thanks.

		Regds,
		New Bee
		--
		Please see the official ORACLE-L FAQ: http://www.orafaq.net
		--
		Author: CHAN Chor Ling Catherine (CSC)
		   INET: clchan_at_nie.edu.sg

		Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
		San Diego, California        -- Mailing list and web hosting services
		---------------------------------------------------------------------
		To REMOVE yourself from this mailing list, send an E-Mail message
		to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
		the message BODY, include a line containing: UNSUB ORACLE-L


		(or the name of mailing list you want to be removed from).  You may

		also send the HELP command for other information (like subscribing).


		_________________________________________________________________
		The new MSN 8: smart spam protection and 2 months FREE*  
		http://join.msn.com/?page=features/junkmail

		-- 
		Please see the official ORACLE-L FAQ: http://www.orafaq.net
		-- 
		Author: M Rafiq
		  INET: rafiq9857_at_hotmail.com

		Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
		San Diego, California        -- Mailing list and web hosting services
		---------------------------------------------------------------------
		To REMOVE yourself from this mailing list, send an E-Mail message
		to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
		the message BODY, include a line containing: UNSUB ORACLE-L


		(or the name of mailing list you want to be removed from).  You may

		also send the HELP command for other information (like subscribing).
		

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: CHAN Chor Ling Catherine (CSC)
  INET: clchan_at_nie.edu.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).