RE: How do you calcuate the temp space needed for view?
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How do you calcuate the temp space needed for view?
There are 2 issues here. First the error. What version of Oracle are you using? Is the TEMP tablespace set up as TEMPORARY? What block size are you using (impacts max extents on older versions of Oracle)? If you are not the only user on the system, you must also account for other sorts that are occuring at the same time. Second, the calculation. The calculation you put forward would be reasonably accurate if you were performing a cartesian product. In the case of the view, how many rows match the criteria. If each table has 1,000,000 rows and the average row length is 1k, then each table consumes roughly 1,024,000,000 bytes. If we plug this into your formula.. (1024 * 100) * (1024 * 100) = 102400 * 102400 = 1048576 or 931 petabytes (1024 gig = 1 petabyte). (Note to self, buy EMC stock). A more accurate method would be to determine how many records will be returned by the query (each table can be queried independently). For each record in table1(parent), how many records will be returned in table2(child)? Get an average, say that for each record in table1, an average of 2 records in table2 will be returned. At this point you need to reverse the numbers for the calculation. Why? Each child will 'attach' to its own copy of the parent record. So, take the size of table2 (# of rows * row length) and add it to the size of table1 (# of rows * row length) * the average number of children. This calculation is not exact and does not account for overhead, hwm, etc. But I have used it in some data warehousing systems (back when disk was not so cheap) and it worked out reasonably well. Dan Fink CHAN Chor Ling Catherine (CSC) wrote: 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: Daniel W. Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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:[EMAIL PROTECTED]] 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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
