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

There is no set formula to assess temp table space for a given query. If your query resulting in Cartesian Join, even temp space of 8-10GB my be insufficient. As I have no 7.3.3.6 database available to test but you can check compatible parameter in your initSID.ora file. and set it to 7.3.3.6 if different and try maxextents script. Clean shutdown and rebounce of database is required to change this param to come into effect.

What is your initial and next extent size? Are you creating new temp tablespace? Try initial and extent extent 10M or larger. As your db_block_size is 2K,allowable maxextents are 121 if you are unable to make it maxextents.

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 20:08:46 -0800

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).


_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. 
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).