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: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
