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

From: Nelson, Allan <anelson_at_midf.com>
Date: Fri, 21 Feb 2003 06:54:31 -0800
Message-ID: <F001.00554452.20030221065431@fatcity.com>

You might not have to increase the tablespace size. Maxextents = 121 is likely to be a bigger part of the problem. Query dba_tablespaces and look at your intial and next values as well as your maxextents parameter. You might find you have enough datafile space and are choking on the default storage clause for this TS. Alter tablespace default storage (initial XXX next XXX maxextents (505 or unlimited or whatever seems right to you) pctincrease (0 if you are using a uniform allocation strategy)) will get you out of this problem. Even adding datafiles will not help you with this specific problem as you are trying to sort something larger than the default storage clause for temp will allow.

Allan

-----Original Message-----
Sent: Thursday, February 20, 2003 7:09 PM To: Multiple recipients of list ORACLE-L

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

This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Nelson, Allan
INET: anelson_at_midf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------