Ravi,
1)Temporary solution:
Around off-peak time do the following to get release of temp tablespace
alter tablespace temp offline;
Just after putting offline , run following immediately
alter tablespace temp online;
2) Create temp tablespace using tempfile like
create temporary tablespace temp
tempfile '/u6/oracle/SID_NAME/data/temp_01.dbf' size 501M
extent management local uniform size 10M;
No need of coalescing or putting online/offline
Regards
Rafiq
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 28 May 2002 05:58:25 -0800
Thank you John,
The things is When we run a partucular query based on
view it takes around 1Gb space from TEMP tablespace
and is not being relased and from that point on words
It give space problem on other txns. that means It
didn't mark fro Free to use does it?
Ravi.
- "Lau, John" <john.lau_at_csfb.com> wrote: > Temporary
segments in temporary tablespaces are
> managed differently - the temporary/sort segment is
> created when the first sort operation that needs to
> write to disk occurs and multiple transactions that
> need to sort to disk can share the same sort segment
> [but use different extents]. The sort segment
> expands by allocating new extents but extents are
> not de-allocated but marked free for re-use in an
> area of the SGA. Each statement that needs to sort
> in the temporary tablespace checks against this part
> of the SGA for free extents. What this means is
> that whilst the instance is up, you will see TEMP
> space apparently being used up but not being
> released [smon de-allocates the sort segment on
> instance startup]. To check actual free space you
> need to query against v$sort_segment.
>
>
> -----Original Message-----
> Sent: 28 May 2002 13:08
> To: Multiple recipients of list ORACLE-L
>
>
> Hi All,
>
> MY DB is 8.1.7 on AIX, TEMP space is not being
> realeasd after sort even after alter tablespace
> <name>
> coalesce. TEMP space was created as temporary.
>
> We didn't event put ant events to not to clear.
> Please let me know if there is any problem.
>
> Cheers,
> Ravi.
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Nalla=20Ravi?=
> INET: vvnrk2001_at_yahoo.co.uk
>
> 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).
>
> This message is for the named person's use only. It
> may contain sensitive and private proprietary or
> legally privileged information. No confidentiality
> or privilege is waived or lost by any
> mistransmission. If you are not the intended
> recipient, please immediately delete it and all
> copies of it from your system, destroy any hard
> copies of it and notify the sender. You must not,
> directly or indirectly, use, disclose, distribute,
> print, or copy any part of this message if you are
> not the intended recipient. CREDIT SUISSE GROUP and
> each legal entity in the CREDIT SUISSE FIRST BOSTON
> or CREDIT SUISSE ASSET MANAGEMENT business units of
> CREDIT SUISSE FIRST BOSTON reserve the right to
> monitor all e-mail communications through its
> networks. Any views expressed in this message are
> those of the individual sender, except where the
> message states otherwise and the sender is
> authorized to state them to be the views of any such
> entity.
> Unless otherwise stated, any pricing information
> given in this message is indicative only, is subject
> to change and does not constitute an offer to deal
> at any price quoted. Any reference to the terms of
> executed transactions should be treated as
> preliminary only and subject to our formal written
> confirmation.
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Lau, John
> INET: john.lau_at_csfb.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).
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Nalla=20Ravi?=
INET: vvnrk2001_at_yahoo.co.uk
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).
MOHAMMAD RAFIQ
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: rafiq9857_at_hotmail.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).
Received on Tue May 28 2002 - 11:38:31 CDT
