Check that the "less than 1%" of sorts aren't huge ones and not done by user whose temporary_tablespace is pointed to permanent one.. system for example.
(Just a note that you can get temporary segments to non-temporary tablespaces due failed index (re)creations, and create table as select) Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 13, 2003 8:54 PM > Tim and Tanel: > > thanks for your help. The temp tablespace is LMTS > and is TEMPORARY and less than 1% of all sorts has > been done on disk. I will make sure to check the > v$locks when this issue appears again. I have changed > the pcticrease for all the tablespaces to 0% so may be > this will take care of the issue. > > Gene > > --- Tim Gorman <[EMAIL PROTECTED]> wrote: > > Tanel hit the nail on the head. In the past, > > ORA-01575 was usually > > associated with temporary tablespaces that were DMT > > and not tablespace type > > "TEMPORARY" (which started in Oracle7.3). First and > > foremost, please make > > sure you are using a TEMPORARY tablespace which is > > locally-managed and uses > > TEMPFILEs... > > > > It might be interesting to monitor V$LOCK for TYPE = > > 'ST' to see what > > sessions are holding this enqueue. If the activity > > is too transient, > > perhaps querying V$SESSION_EVENT where EVENT = > > 'enqueue' might indirectly > > imply which sessions have waited on an enqueue (not > > necessarily "ST", > > thought!) sometime in the past... > > > > > > > > on 8/13/03 7:04 AM, Tanel Poder at > > [EMAIL PROTECTED] wrote: > > > > > Hi! > > > > > > You can always schedule alter tablespace > > coalesce's during low usage time. > > > But you should check whether you have adjacent > > free extents in your > > > tablespaces at all? If you're not doing lot's of > > dropping or truncating > > > objects, then you shouldn't have. Thus no need for > > coalesce either. Just > > > check that all of your sort segments go to the > > temp tablespace (which should > > > be in temporary mode, preferrably LMT as well). > > > > > > Tanel. > > > > > > > > >> thanks for the info. We do have a number of DMTS > > in > > >> the database. Three of them have pct_increase of > > 50%, > > >> the rest - 0. Should I consider changing the > > >> pct_increase to 0 in all tablespaces in order to > > get > > >> rid of this ora 1575? Wouldn't I want to have an > > >> automatic coalesce process for the DMTS though? > > >> > > >> thank you > > >> > > >> Gene > > >> --- Tim Gorman <[EMAIL PROTECTED]> wrote: > > >>> Haven't seen this error since Oracle7... > > >>> > > >>> If the message is hitting the "alert.log", then > > >>> chances are good it is > > >>> coming from SMON. SMON is attempting to acquire > > the > > >>> "ST" (a.k.a. Space > > >>> transaction) enqueue in preparation for > > coalescing > > >>> free space in some > > >>> tablespaces. However, if it is unable to > > acquire > > >>> "ST" after a couple > > >>> seconds, it times out and issues ORA-01575 to > > the > > >>> alert.log. > > >>> > > >>> So, based on experiences from 6-7 years ago: > > >>> > > >>> * do you have a lot of "dictionary-managed" > > >>> tablespaces? > > >>> * do these DMT's have default PCTINCREASE > > >>> non-zero, thus attacting > > >>> SMON to do coalescing? > > >>> > > >>> If so, I'd suggest going to "locally-managed" > > >>> tablespaces if at all > > >>> possible... > > >>> > > >>> > > >>> > > >>> on 8/12/03 12:44 PM, Gurelei at > > [EMAIL PROTECTED] > > >>> wrote: > > >>> > > >>>> Hi all: > > >>>> > > >>>> I'm seeing the ora-01575 error in the alert > > >>> logfile. > > >>>> The article on the metalink refers to the > > >>> parameter > > >>>> which I think is obsolete in the ORacle version > > we > > >>> are > > >>>> running (8.1.7). What does this error refer to? > > >>> Any > > >>>> thoughts? references? > > >>>> > > >>>> thanks > > >>>> > > >>>> gene > > >>>> > > >>>> __________________________________ > > >>>> Do you Yahoo!? > > >>>> Yahoo! SiteBuilder - Free, easy-to-use web site > > >>> design software > > >>>> http://sitebuilder.yahoo.com > > >>> > > >>> -- > > >>> Please see the official ORACLE-L FAQ: > > >>> http://www.orafaq.net > > >>> -- > > >>> Author: Tim Gorman > > >>> 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). > > >> > > >> > > >> __________________________________ > > >> Do you Yahoo!? > > >> Yahoo! SiteBuilder - Free, easy-to-use web site > > design software > > >> http://sitebuilder.yahoo.com > > >> -- > > >> Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > >> -- > > >> Author: Gurelei > > >> 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: Tim Gorman > > 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). > > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Gurelei > 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: Tanel Poder 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).
