Re: ora 1575?

From: Tim Gorman <tim_at_sagelogix.com>
Date: Wed, 13 Aug 2003 06:29:22 -0800
Message-ID: <F001.005CA7B0.20030813062922@fatcity.com>

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 tanel.poder.003_at_mail.ee 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 <tim_at_sagelogix.com> 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 gurelei_at_yahoo.com
>>> 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: tim_at_sagelogix.com
>>>
>>> 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).

>> 
>> 
>> __________________________________
>> 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: gurelei_at_yahoo.com
>> 
>> 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).
>> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

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

Received on Wed Aug 13 2003 - 09:29:22 CDT