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

M Rafiq Fri, 21 Feb 2003 11:56:32 -0800

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

_________________________________________________________________ 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: [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?

Reply via email to