RE: TEMP segment is not being realsed.
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
Re: TEMP segment is not being realsed.
there is no problem. Oracle leaves the temp segment allocated (you should be seeing only one) when your temp tablespace is created temporary to save allocation time for future sorts. It will go away when/if you shutdown and restart the database, but it is SUPPOSED to be there --- Nalla Ravi [EMAIL PROTECTED] wrote: 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: TEMP segment is not being realsed.
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
