Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ora 1575?

Re: ora 1575?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Wed, 13 Aug 2003 10:14:39 -0800
Message-ID: <F001.005CA817.20030813101439@fatcity.com>


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" <ORACLE-L_at_fatcity.com> 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 <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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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 - 13:14:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US