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