I also faced a similar problem when trying to do some
large sorts. I had to wait for the temporary segments
to be cleaned up on my regular table space(I have a
temp tablespace available). I am just curious to find
out when Oracle uses the non temporary table spaces
for sorts when it has a specific temp table space
assigned and available.
Infact I posted this problem on the list 2 weeks ago
with out any responses.
Thanks,
-Ravi
- "Miller, Jay" <JayMiller_at_TDWaterhouse.com> wrote:
> Yes, that's correct. So now I can officially say
> that I have no idea why
> that wasn't the case for you :).
>
> -----Original Message-----
> Sent: Wednesday, June 07, 2000 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> Up to an hour.
>
> Is it correct to assume that extents allocated, for
> sort use,
> will not turn into "free space" but will be "free
> for use" by other
> transactions
> once they are no longer inuse?
>
>
>
>
>
> "Miller, Jay"
> <JayMiller_at_tdwaterhouse.com>@fatcity.com on
> 06/07/2000
> 10:15:11 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> Sent From the mail file of: Dan Hubler
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
> Hmm, SMON only wakes up to clean up temp segments
> every 5 minutes. How
> long
> after the SQL doing the sort was killed were you
> having the problems before
> you did the ALTER TABLESPACE command?
>
> -----Original Message-----
> Sent: Monday, June 05, 2000 6:00 PM
> To: ORACLE-L_at_fatcity.com
> Cc: jaymiller_at_tdwaterhouse.com
>
>
>
>
> I think I understand what you are saying.
> But....................
> My situation was that the extents were not being
> "released".
> That is, the space was unavailable for other
> transactions to use.
> We were failing with space errors.
>
> Also, we do have the tablespace defined as
> TEMPORARY.
>
> Thanks.
>
>
>
>
>
>
> "Miller, Jay"
> <JayMiller_at_tdwaterhouse.com>@fatcity.com on
> 06/05/2000
> 03:05:08 PM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
>
> Sent From the mail file of: Dan Hubler
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
> If the tablespace is of type temporary then the
> extents do not get
> released.
> They remain allocated (but unused)once the initial
> sort is done. This is a
> good thing, as it reduces the processing necessary
> for extent allocation.
>
> -----Original Message-----
> Sent: Monday, June 05, 2000 3:03 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Looking for some information on the following:
>
> We had an incident last week where an Oracle
> instance (7.3.4.4) on NT
> was running into problems acquiring space for
> additional extents on
> the defined temporary tablespace.
>
> While looking at the situation, we discovered that
> there was just about
> zero
> free space left in the tablespace. There was a
> whole bunch of extents
> created and owned by "SYS" and they had some flaky
> numeric
> identifier for the segment-name (something like
> 37.123).
>
> Other processes were failing, being unable to
> allocate temp space.
>
> We looked around on Metalink, and discovered a note
> about
> forcing temporary segments/extents to be cleaned up
> by
> performing an "ALTER TABLESPACE TEMP DEFAULT STORAGE
> (PCTINCREASE 0);"
>
> We executed the statement. Lo and behold, all the
> segments disappeared,
> back into
> free space.
>
> My guess is that there was a large sort (we do some
> of those) that failed,
> and left segments out there. I do not understand
> why they wouldn't clean
> up.
>
> Any information?
>
>
>
> --
> Author:
> INET: Dan.Hubler_at_midata.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> 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).
> --
> Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> 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).
>
> --
> Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
Received on Wed Jun 07 2000 - 18:28:03 CDT
