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 <tim_at_sagelogix.com> 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
> 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).
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).
Received on Wed Aug 13 2003 - 12:54:28 CDT
