Who is using temp tablespace?
Hi All, How can I determine who is using temp tablespace? I have a 1 gb tablespace and it is almost full. I want to know who and what sql is using most of it. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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: Who is using temp tablespace?
v$sort_usage and v$session should give you that. Pawan -Original Message- Sent: Tuesday, November 04, 2003 6:49 PM To: Multiple recipients of list ORACLE-L Hi All, How can I determine who is using temp tablespace? I have a 1 gb tablespace and it is almost full. I want to know who and what sql is using most of it. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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 contains information that may be privileged or confidential and is the property of the Cap Gemini Ernst Young Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorised to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Satav, Pawan INET: [EMAIL PROTECTED] 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: [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: Who is using temp tablespace?
Rick , This query will help you to find who is using the space SELECT a.username, a.osuser, a.sid||','||a.serial# SID_SERIAL, c.spid Process, b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space FROM v$session a,v$sort_usage b, v$process c, dba_tablespaces d WHEREa.saddr = b.session_addr AND a.paddr = c.addr AND b.tablespace=d.tablespace_name --ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks group by a.username, a.osuser, a.sid||','||a.serial#,c.spid, b.tablespace, a.status Thanks Shibu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tue 11/4/2003 6:49 PM To: Multiple recipients of list ORACLE-L Cc: Subject: Who is using temp tablespace? Hi All, How can I determine who is using temp tablespace? I have a 1 gb tablespace and it is almost full. I want to know who and what sql is using most of it. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. winmail.dat
Re: ** temp tablespace
Hi! How much free space you have in your file system, you might be hitting the sparse files issue... Tanel. - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Thursday, October 23, 2003 10:49 PM Subject: ** temp tablespace Hi, I have a huge temp tablespace (4GB)and not much usage (current usage is 50MBfrom v$ tables) . However it keeps running out of space. It ran out of space yesterday and I was hoping it would release but it got an error again. The database is only 35GB mostly small tables. IT is temporary (contents). How can i tell how much is *actually* free. dba_free_space shows hardly any free even when there is no activity. Thanks Do you Yahoo!?The New Yahoo! Shopping - with improved product search
** temp tablespace
Hi, I have a huge temp tablespace (4GB)and not much usage (current usage is 50MBfrom v$ tables) . However it keeps running out of space. It ran out of space yesterday and I was hoping it would release but it got an error again. The database is only 35GB mostly small tables. IT is temporary (contents). How can i tell how much is *actually* free. dba_free_space shows hardly any free even when there is no activity. Thanks Do you Yahoo!? The New Yahoo! Shopping - with improved product search
RE: ** temp tablespace
4GB isn't all that huge. A couple of questions I can think of are: 1. How many users are simultaneously trying to sort stuff? 2. How often does some dufus fire up a Cartesian join? -Original Message- Hi, I have a huge temp tablespace (4GB) and not much usage (current usage is 50MB from v$ tables) . However it keeps running out of space. It ran out of space yesterday and I was hoping it would release but it got an error again. The database is only 35GB mostly small tables. IT is temporary (contents). How can i tell how much is *actually* free. dba_free_space shows hardly any free even when there is no activity. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [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).
Temp Tablespace
Title: RE: SAME and separating disk and index tablespaces All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message-From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 9:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of "human effort" in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older "rules of thumb" aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that "thou shalt always separate indexes from tables" may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes t
RE: Temp Tablespace
Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may have partitioning and other requirements such as parallelism that impact the placement and availability of your data. This in turn will control the number of logical volumes that need to be created. I think the idea and philosophy behind SAME is noble - Use all available drives, so that you do not have localized hot-spots
Re: Temp Tablespace
If it is a TABLESPACE TEMPORARY, query on v$SORT_SEGMENT to get the current size of the sort segment. Then issue an ALTER TABLESPACE tablespacename DEFAULT STORAGE (PCTINCREASE 0). Re-query V$SORT_SEGMENT and the segment would have been released. If there were current users in the segment, you would still see a TEMPORARY segment in DBA_SEGMENTS, though, and the disk-space would not be released till those users log out. Hemant At 06:14 AM 09-10-03 -0800, you wrote: All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- From: Loughmiller, Greg [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- From: vikas kawatra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: SAME and separating disk and index tablespaces Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise
Re: Temp Tablespace
Tom, What type of temporary? Dictionery managed or LMT. If dictionary managed then 2 solutions. a)make it offline and online again during offpeak period. 2)Make it permanent tablespace to avoid this situation. If LMT based (temp_datafiles) then check v$sort_usage instead of header table. If header table says 100% it is normal but if v$sort_usage indicates 100% then it is a problem and then you have to either bounce the db and if this is not the option then create another samller temp1 tablespace.point your user to that tempspace , drop your original temp tbs and recreate it and then point your user back to original temp tbs. HTH Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 06:14:27 -0800 All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best
RE: Temp Tablespace
Title: RE: SAME and separating disk and index tablespaces Hi: I had this situation last month and I finally bounced the db. "drop tablespace temp INCLUDING CONTENTS" might work if you want to wait long enough. I tried this and the db just hang for 30 min before I killed it. My temp ts was 10G. Here is a copy of an old email I sent to the list last month: Yes, you are right, I should have looked at ts# = 2. BTW, I tried several ways to resolve this issue by doing these and none of them worked, 1. alter tablespace temp default storage(pctincrease 0); -- a tip from Steve Adam's site 2. alter session set events 'immediate trace name DROP_SEGMENTS level 3'; 3. oradebug wakeup 6; -- 6 is sid for SMON process 4. Create a small table with a primary key constraint then drop the constraint. Iwas hoping that shadow process will change the associated index to a temporary segment and post smon to clear it up. 5. create a large table that will fail: CREATE TABLE junk ( c1 varchar2(1) ) tablespace DATA storage ( initial 2000M next 2000M) ; Finally I had to bounce the instance, that cleared up the segment in temp tablespace, so that it could be dropped. Guang -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of zhu chao Sent: Tuesday, September 16, 2003 10:25 PM To: Multiple recipients of list ORACLE-L Subject: Re: drop tablespace temp INCLUDING CONTENTS hang Hi, guang: First, I think step 5,6,7 are not necessary. Why bother change back to that named tablespace? To user, temporary tablespace name is totally transparent to them and to the application. You should use drop tablespace temp including contents, but the query you used maybe is wrong. your temp tablespace is TS#=2, but your query is :select count(*) from fet$ where ts# = 3; (you are using 3 here). Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 4:49 AM Hi: Oracle 8173 on Solaris 2.8. I am trying to convert temp tablespace to LMT. My plan is to 1.create another temp tablespace temp123 2.move all users to temp123 3.alter tablespace temp offline 4.drop tablespace temp 5.re-create temp tablespace as LMT 6.move all users to temp 7.drop tablespace temp123. But I am having problem in Step4. I found that there are still some objects in temp ts even if I put it offline: [EMAIL PROTECTED] select count(*) from dba_segments where tablespace_name='TEMP'; COUNT(*) -- 1 "drop tablespace temp" resulted in "ORA-01549 tablespace not empty, use INCLUDING CONTENTS option". However when I did drop tablespace temp INCLUDING CONTENTS; the sqlplus session just hang and I kept getting the same count(*) from the following query (from another session) [EMAIL PROTECTED] select count(*) from fet$ where ts# = 3; COUNT(*) -- 214 I eventually killed the orginal sqlplus session. I did "ALTER TABLESPACE TEMP coalesce" and it did not seem to help. So my question is: How can I drop an offlined temp tablespace when there is still segment(s) in it? I don't know if SMON would clean up the temp ts automatically (Some doc says SMON would not do to TEMP ts). I also read that I could issue alter session set events 'immediate trace name DROP_SEGMENTS level 3'; to mimic SMON, becuase [EMAIL PROTECTED] select name, ts# from ts$ where NAME='TEMP'; NAME TS# -- -- TEMP 2 But I am not ready to try it on our production system. Has anyone tries this or has any suggestions? I don't want to bounce db. Would the objects in TEMP ts be cleaned up by SMON if I wait for a couple of days? TIA. Guang -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Mercadante, Thomas FSent: Thursday, October 09, 2003 10:14 AMTo: Multiple recipients of list ORACLE-LSubject: Temp Tablespace All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional
RE: Temp Tablespace
I think you are right Jack. With a Temporary Temp tablespace, there is nothing I can do to lower the HWM. And I don;t really think it's full - just the HWM shows it filled up. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data is always the best justification mechnism for a configuration exercise such as this. Plus, you may
RE: Temp Tablespace
Under the senario rely on v$sort_usage view. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 09:24:24 -0800 I think you are right Jack. With a Temporary Temp tablespace, there is nothing I can do to lower the HWM. And I don;t really think it's full - just the HWM shows it filled up. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Hi A.F.A.I.K the space is free for use by new queries. Oracle just does not release the extents to save resources on extent management. Just that your monitoring scripts keep yelling 100% used :-) Jack -Original Message- Sent: Thursday, October 09, 2003 4:14 PM To: Multiple recipients of list ORACLE-L All, Is there a way to clear a TEMP tablespace defined as Temporary? We had a couple of long-running jobs that have totally clogged up the TEMP tablespace (54 Gig worth) and it doesn't seem to be releasing the space. I know a db restart will clear it. Any other ideas? 8.1.7.4 by the way. thanks Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 09, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of human effort in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older rules of thumb aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us. just a comment:-) greg -Original Message- Sent: Wednesday, October 08, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Great responses ! Thanks very much .. -Original Message- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Hi Gaja, I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential. So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel. Regards, Dave [EMAIL PROTECTED] wrote: Hi Hans/Vikas, I tend to agree that the old draconian rule that thou shalt always separate indexes from tables may not apply any more. We used to apply that principle in the past when the number of available spindles was not adequate. Seems like with 256G drives in the market, we are being pushed back in time, in some way!!! The way I look at the problem is purely from an IOPS perspective. For example, if each physical disk is capable of 256 IOPS (ignore the cache configured here) and you have 10 disks in your volume, then the total I/O capacity on this volume is 2560 IOPS. Separation of objects across multiple volumes may becomes an issue, only when the demand for I/O outstrips the supply (in this case 2560 IOPS). Even then, you can always add more drives to the existing volume and restripe, i.e., adding 5 more drives to 10 drives increases the I/O capacity by 50%. At the end of the day, the I/O sub-system does not care, whether it is servicing a data segment, index segment or undo segment. But, in certain environments, that I have dealt with, there has been a need to separate heavily and concurrently accessed objects (does not matter whether these objects are all indexes or tables or both). This may be true only for certain objects and certain queries. So, please don't apply this in a blanket fashion. Empirical data
Re: TEMP Tablespace problem
In 9i you could issue an drop tablespace temp including contents and datafiles. (First make sure that this tablespace is not a default temporary tablespace). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 5:54 AM What is the Oracle version? At 06:24 PM 9/30/2003 -0800, you wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [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 Tablespace problem
9.2i [EMAIL PROTECTED] 09/30/03 08:54PM What is the Oracle version?At 06:24 PM 9/30/2003 -0800, you wrote:Hi!!I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller.Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday).Wolfgang BreitlingCentrex Consulting Corporationhttp://www.centrexcc.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: TEMP Tablespace problem
This table do not have any file, how I understood this is the table space that the queries uses. They tell me that if I run a query that need mode that the actual space it will be made that the TEMP tablespace grow. [EMAIL PROTECTED] 09/30/03 10:09PM If the TEMP tablespace is a temporary tablespace, i.e. made of temp files rather than datafiles, then you can't offline it. It would have to be dropped and rebuilt.At 07:34 PM 9/30/2003 -0800, you wrote:Maybe you can create another temp tablespace (called temp_new) on anotherdisk, assign all users to temp_new, then offline the old temp tablespace,drop the old temp tablespace, and finally remove the old temp datafilesfrom OS.Wolfgang BreitlingOracle7, 8, 8i, 9i OCP DBACentrex Consulting Corporationhttp://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: TEMP Tablespace problem
If you can stop the users then simply drop the tablespace and remove the datafile, then crate new smaller temp. If you can not stop the users do: 1) Create newsmall temp; 2) Alter all users to use the new temp. 3) Drop temp , after you are sure that none of the users is using it. Yechiel AdarMehish - Original Message - From: Teresita Castro To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 01, 2003 4:19 PM Subject: Re: TEMP Tablespace problem 9.2i [EMAIL PROTECTED] 09/30/03 08:54PM What is the Oracle version?At 06:24 PM 9/30/2003 -0800, you wrote:Hi!!I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller.Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday).Wolfgang BreitlingCentrex Consulting Corporationhttp://www.centrexcc.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: TEMP Tablespace problem
Where did you look for this file? Use v$tempfile or dba_temp_files. Anyway, your case is a good reason why not to enable autoextend in temp and rbs tablespaces without extra care. If you got DBA access to your database, you could: 1) create temporary tablespace new_temp 2) alter database default temporary tablespace new_temp 3) drop tablespace temp including contents and datafiles (might take time) 4) create temporary tablespace temp ... 5) alter database default temporary tablespace temp 6) drop tablespace new_temp including contents and datafiles If you don't have DBA access nor OS access, then you have to wait until your administrator comes back or hack yourself into serverroom to get physical access to your server. Tanel. - Original Message - From: Teresita Castro To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 01, 2003 5:39 PM Subject: Re: TEMP Tablespace problem This table do not have any file, how I understood this is the table space that the queries uses. They tell me that if I run a query that need mode that the actual space it will be made that the TEMP tablespace grow. [EMAIL PROTECTED] 09/30/03 10:09PM If the TEMP tablespace is a temporary tablespace, i.e. made of temp files rather than datafiles, then you can't offline it. It would have to be dropped and rebuilt.At 07:34 PM 9/30/2003 -0800, you wrote:Maybe you can create another temp tablespace (called temp_new) on anotherdisk, assign all users to temp_new, then offline the old temp tablespace,drop the old temp tablespace, and finally remove the old temp datafilesfrom OS.Wolfgang BreitlingOracle7, 8, 8i, 9i OCP DBACentrex Consulting Corporationhttp://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: TEMP Tablespace problem
I have Oracle 9.2i I already run the query and the tablespace TEMP have CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL Can I run this line to fix the size of my tablespace? SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; I mean is the same or I have to change something? [EMAIL PROTECTED] 09/30/03 10:04PM Teresita, spelling the word "administrator" with the capital letteris a good thing. Furthermore, if you have version 9i, you can do thefollowing, little known, thing:SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M;Database altered.If it's Oracle 8i, you can actually have a permanent, dictionary based tablespace acting as a temporary tablespace. In version 9, however, youcan't. Here is the query to find out what do you have: 1 select tablespace_name,contents,extent_management 2 from dba_tablespaces 3* order by contentsSQL /TABLESPACE_NAME CONTENTS EXTENT_MAN-- - --SYSTEM PERMANENT LOCALDRSYS PERMANENT LOCALEXAMPLE PERMANENT LOCALINDX PERMANENT LOCALUSERS PERMANENT LOCALXDB PERMANENT LOCALTOOLS PERMANENT LOCALTEMP TEMPORARY LOCALUNDOTBS1 UNDO LOCAL9 rows selected.SQLIf the column contents reads "TEMPORARY" for the given tablespace, you candrop it and recreate it without any harm. Note that in 8i you don't have "UNDO" tablespaces and your system tablespace cannot be LMT.On 2003.09.30 22:24, Teresita Castro wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday).-- Mladen GogalaOracle DBA-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mladen Gogala INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: TEMP Tablespace problem
Yes you can. On Wed, 2003-10-01 at 11:29, Teresita Castro wrote: I have Oracle 9.2i I already run the query and the tablespace TEMP have CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL Can I run this line to fix the size of my tablespace? SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; I mean is the same or I have to change something? [EMAIL PROTECTED] 09/30/03 10:04PM Teresita, spelling the word administrator with the capital letter is a good thing. Furthermore, if you have version 9i, you can do the following, little known, thing: SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; Database altered. If it's Oracle 8i, you can actually have a permanent, dictionary based tablespace acting as a temporary tablespace. In version 9, however, you can't. Here is the query to find out what do you have: 1 select tablespace_name,contents,extent_management 2 from dba_tablespaces 3* order by contents SQL / TABLESPACE_NAMECONTENTS EXTENT_MAN -- - -- SYSTEM PERMANENT LOCAL DRSYS PERMANENT LOCAL EXAMPLEPERMANENT LOCAL INDX PERMANENT LOCAL USERS PERMANENT LOCAL XDBPERMANENT LOCAL TOOLS PERMANENT LOCAL TEMP TEMPORARY LOCAL UNDOTBS1 UNDO LOCAL 9 rows selected. SQL If the column contents reads TEMPORARY for the given tablespace, you can drop it and recreate it without any harm. Note that in 8i you don't have UNDO tablespaces and your system tablespace cannot be LMT. On 2003.09.30 22:24, Teresita Castro wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, 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. Wang Trading LLC and any of its subsidiaries each 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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 Tablespace problem
I don't think that you can reduce temp datafile much below bytes_used value in v$temp_space_header. I could reduce the file few kilobytes, but not much compared to it's size. In order to reduce bytes_used, you need to bounce instance (if there isn't any nifty tricks for releasing temp segment otherwise). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 6:34 PM Yes you can. On Wed, 2003-10-01 at 11:29, Teresita Castro wrote: I have Oracle 9.2i I already run the query and the tablespace TEMP have CONTENTS=TEMPORARY AND EXTENT_MAN=LOCAL Can I run this line to fix the size of my tablespace? SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; I mean is the same or I have to change something? [EMAIL PROTECTED] 09/30/03 10:04PM Teresita, spelling the word administrator with the capital letter is a good thing. Furthermore, if you have version 9i, you can do the following, little known, thing: SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; Database altered. If it's Oracle 8i, you can actually have a permanent, dictionary based tablespace acting as a temporary tablespace. In version 9, however, you can't. Here is the query to find out what do you have: 1 select tablespace_name,contents,extent_management 2 from dba_tablespaces 3* order by contents SQL / TABLESPACE_NAMECONTENTS EXTENT_MAN -- - -- SYSTEM PERMANENT LOCAL DRSYS PERMANENT LOCAL EXAMPLEPERMANENT LOCAL INDX PERMANENT LOCAL USERS PERMANENT LOCAL XDBPERMANENT LOCAL TOOLS PERMANENT LOCAL TEMP TEMPORARY LOCAL UNDOTBS1 UNDO LOCAL 9 rows selected. SQL If the column contents reads TEMPORARY for the given tablespace, you can drop it and recreate it without any harm. Note that in 8i you don't have UNDO tablespaces and your system tablespace cannot be LMT. On 2003.09.30 22:24, Teresita Castro wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, 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. Wang Trading LLC and any of its subsidiaries each 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel
TEMP Tablespace problem
Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday).
Re: TEMP Tablespace problem
What is the Oracle version? At 06:24 PM 9/30/2003 -0800, you wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [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 Tablespace problem
Maybe you can create another temp tablespace (called temp_new) on another disk, assign all users to temp_new, then offline the old temp tablespace, drop the old temp tablespace, and finally remove the old temp datafiles from OS. HTH. Guang On Tue, 30 Sep 2003, Teresita Castro wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [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 Tablespace problem
Teresita, spelling the word administrator with the capital letter is a good thing. Furthermore, if you have version 9i, you can do the following, little known, thing: SQL alter database tempfile '/data/oradata/system/temp01.dbf' resize 128M; Database altered. If it's Oracle 8i, you can actually have a permanent, dictionary based tablespace acting as a temporary tablespace. In version 9, however, you can't. Here is the query to find out what do you have: 1 select tablespace_name,contents,extent_management 2 from dba_tablespaces 3* order by contents SQL / TABLESPACE_NAMECONTENTS EXTENT_MAN -- - -- SYSTEM PERMANENT LOCAL DRSYS PERMANENT LOCAL EXAMPLEPERMANENT LOCAL INDX PERMANENT LOCAL USERS PERMANENT LOCAL XDBPERMANENT LOCAL TOOLS PERMANENT LOCAL TEMP TEMPORARY LOCAL UNDOTBS1 UNDO LOCAL 9 rows selected. SQL If the column contents reads TEMPORARY for the given tablespace, you can drop it and recreate it without any harm. Note that in 8i you don't have UNDO tablespaces and your system tablespace cannot be LMT. On 2003.09.30 22:24, Teresita Castro wrote: Hi!! I am trying to change the size of my tablespace TEMP, I am not an Administrator but we really need to make this tablespace smaller. Already the size is 13214 Mgs, and this tablaspace is on a disk that is full, so if we can not make it smaller we are going to be in a serius trouble ( our Administrator is not here until Monday). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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 Tablespace problem
If the TEMP tablespace is a temporary tablespace, i.e. made of temp files rather than datafiles, then you can't offline it. It would have to be dropped and rebuilt. At 07:34 PM 9/30/2003 -0800, you wrote: Maybe you can create another temp tablespace (called temp_new) on another disk, assign all users to temp_new, then offline the old temp tablespace, drop the old temp tablespace, and finally remove the old temp datafiles from OS. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] 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: [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: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace I'm even more confused now. TEMP ran out of space at 20GB - I'm only inserting 12.2GB! So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert. -Original Message- From: Turner, Adrian A SITI-ITPSIE Sent: 16 September 2003 15:16 To: Multiple recipients of list ORACLE-L Subject: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
RE: Nosort parallel dml uses TEMP tablespace
Thanks for the input Praveen, if only it were that easy. The sql is a straight insert into tab1 select * from tab2 but with a parallel dml hint. Remove the parallel and it doesnt use TEMP. Theres no distinct, group by, order by or indexes on the target table (there are 12 or so on the source table). As I said I've been reading metalink notes like mad.. Note:50592.1 says Parallel Insert SYNTAX: ALTER session enable parallel dml; INSERT /*+ APPEND PARALLEL(d2 4) */ into d2 SELECT ...HOW IT WORKS: Each slave creates a TEMPORARY segment in target tablespace with INITIAL=NEXT from the tables storage clause. Each slave then populates its own segment in a similar manner to parallel SQLLOAD except that the rows are taken from the SELECT row source. On completion of the INSERT segments are MERGED one TEMPORARY segment with all trailing extents being trimmed - Note: we DO trim the temporary segment that we are merging into. So apparently Each slave creates a TEMPORARY segment in target tablespace but I am getting them appearing in the TEMP tablespace, not the tablespaces containing the partitions. And the volume of TEMP required does not compute either. BTW there are 27 partitions in the target table, which does not match the number parallel slaves Cheers, Adrian -Original Message- Sent: 17 September 2003 09:26 To: Turner, Adrian A SITI-ITPSIE Cc: [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L; [EMAIL PROTECTED] Adrian, Please see the 2nd point. This will always use Temporary tablespace even if you have some other tablespace for the table getting inserted. Operations Requiring Sorting Index creation Parallel insert operation involving index maintenance ORDER BY or GROUP BY clauses DISTINCT values selection UNION, INTERSECT, or MINUS operators Sort-merge joins ANALYZE command execution Please ignore this if you know it already. The Sort Process The Oracle server sorts in memory if the work can be done within an area smaller than the value (in bytes) of the parameter SORT_AREA_SIZE. If the sort needs more space than this value: 1 The data is split into smaller pieces, called sort runs; and each piece is sorted individually. 2 The server process writes pieces to temporary segments on disk; these segments hold intermediate sort runs data while the server works on another sort run. 3 The sorted pieces are merged to produce the final result. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes. Regards, Praveen __ Praveen Shetty Oracle DBA Technology Solutions, IS. National Grid Transco, Hinckley. Phone: 715-32703 (Int) ,+44-1455-892703 (Ext) mail 2:[EMAIL PROTECTED] __ Vohra Vishal 17/09/03 09:04 To: Pathania Birinder/PB251/Solihull/[EMAIL PROTECTED], Praveen Shetty/PKS02/Solihull/[EMAIL PROTECTED] cc: Subject: RE: Nosort parallel dml uses TEMP tablespace Gud One ! ! ! !! Regards, Vishal Vohra EBMS 715-32569 Mob:07952883716 - Forwarded by Vohra Vishal/VV011/Solihull/Transco on 17/09/03 09:09 - Turner, Adrian A SITI-ITPSIE [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ell.com cc: Sent by: Subject: RE: Nosort parallel dml uses TEMP tablespace [EMAIL PROTECTED
Re: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace Hi! It could be that parallel slaves are passing intermediate results using TEMP tablespace for some reason. (When table queues are full in some cases or smth like that). It can depend on parallel execution plan as well (whether it's parallel_to_parallel or parallel_to_serial data distribution). If you used only /*+ APPEND */ without parallel, then operations were done serially by one session only, thus no intermediate result passing between slaves (using table queues) were needed. You might want to post your question to comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering more advanced performance related questions there. Tanel. - Original Message - From: Turner, Adrian A SITI-ITPSIE To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 11:39 AM Subject: RE: Nosort parallel dml uses TEMP tablespace I'm even more confused now. TEMP ran out of space at 20GB - I'm only inserting 12.2GB! So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert. -Original Message- From: Turner, Adrian A SITI-ITPSIE Sent: 16 September 2003 15:16 To: Multiple recipients of list ORACLE-L Subject: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
Re: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace Hi! Btw, I did a little testing with PARALLEL 2, and yes it seems to be a parallel slave communications issue, despite the segments show type "SORT". When i traced sorting with 10032 10033, I saw sort segment allocation, but no real sorting occurring. When I compared first rows in source table with target one,I saw that when in source table I had rows (1,2,3,4,5,6,7), then in beginning of target table, I had (1,3,5,7,etc..). That means I had only one session reading data (query coordinator), which then evenly distributed data to it's slaves. You should includePARALLEL hint to your select statement as well, with same number of slaves (in addition to insert +append one), that way PX can work parallel_to_parallel way, thus for each producing (query) slave there is a consuming slave and data doesn't have to be distributed or splitted from one source to several queues (like with parallel_from_serial). That way no data is intermediately stored to temporary segments. This worked for me, but since PX is a complex mechanism, youmight see different results. Tanel. - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 2:44 PM Subject: Re: Nosort parallel dml uses TEMP tablespace Hi! It could be that parallel slaves are passing intermediate results using TEMP tablespace for some reason. (When table queues are full in some cases or smth like that). It can depend on parallel execution plan as well (whether it's parallel_to_parallel or parallel_to_serial data distribution). If you used only /*+ APPEND */ without parallel, then operations were done serially by one session only, thus no intermediate result passing between slaves (using table queues) were needed. You might want to post your question to comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering more advanced performance related questions there. Tanel. - Original Message - From: Turner, Adrian A SITI-ITPSIE To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 17, 2003 11:39 AM Subject: RE: Nosort parallel dml uses TEMP tablespace I'm even more confused now. TEMP ran out of space at 20GB - I'm only inserting 12.2GB! So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert. -Original Message- From: Turner, Adrian A SITI-ITPSIE Sent: 16 September 2003 15:16 To: Multiple recipients of list ORACLE-L Subject: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
RE: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace
Thanks
for having a look Tanel; that makes great sense.
I'll
have a quick play to convince myself but I'd say its a lesson
learned.
BTW
did you too experience more temp allocation than you would have
expected?
Thanks
again,
Adrian
-Original Message-From: Tanel Poder
[mailto:[EMAIL PROTECTED]Sent: 17 September 2003
14:00To: Multiple recipients of list ORACLE-LSubject:
Re: Nosort parallel dml uses TEMP tablespace
Hi!
Btw, I did a little testing with PARALLEL 2, and
yes it seems to be a parallel slave communications issue, despite the segments
show type "SORT".
When i traced sorting with 10032 10033, I
saw sort segment allocation, but no real sorting occurring. When I compared
first rows in source table with target one,I saw that when in source
table I had rows (1,2,3,4,5,6,7), then in beginning of target table, I had
(1,3,5,7,etc..). That means I had only one session reading data (query
coordinator), which then evenly distributed data to it's slaves.
You should includePARALLEL hint to your
select statement as well, with same number of slaves (in addition to insert
+append one), that way PX can work parallel_to_parallel way, thus for each
producing (query) slave there is a consuming slave and data doesn't have to be
distributed or splitted from one source to several queues (like with
parallel_from_serial). That way no data is intermediately stored to temporary
segments.
This worked for me, but since PX is a complex
mechanism, youmight see different results.
Tanel.
- Original Message -
From:
Tanel
Poder
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 17, 2003
2:44 PM
Subject: Re: Nosort parallel dml uses
TEMP tablespace
Hi!
It could be that parallel slaves are passing
intermediate results using TEMP tablespace for some reason. (When table
queues are full in some cases or smth like that). It can depend on parallel
execution plan as well (whether it's parallel_to_parallel or
parallel_to_serial data distribution).
If you used only /*+ APPEND */ without
parallel, then operations were done serially by one session only, thus no
intermediate result passing between slaves (using table queues) were needed.
You might want to post your question to
comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering
more advanced performance related questions there.
Tanel.
- Original Message -
From:
Turner, Adrian A SITI-ITPSIE
To: Multiple recipients of list
ORACLE-L
Sent: Wednesday, September 17, 2003
11:39 AM
Subject: RE: Nosort parallel dml uses
TEMP tablespace
I'm even more confused now.
TEMP ran out of space at 20GB -
I'm only inserting 12.2GB!
So I kicked it off again with the
hint just as /*+ APPEND */ and it completed successfully in 1/6th the time
to failure of the parallel insert.
-Original
Message- From: Turner, Adrian A
SITI-ITPSIE Sent: 16
September 2003 15:16 To: Multiple recipients of list ORACLE-L
Subject:
Nosort parallel dml uses TEMP
tablespace
Apologies if this has been
covered before but metalink is not clear on the reasons behind
it
The database is running
version 9204 EE on WinNT Sp6; and the statement is a parallel direct
load into partitioned table selecting from a 12.2GB source
table.
ALTER SESSION
ENABLE PARALLEL DML ;
INSERT /*+ APPEND
PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW
(select * from
TRANSACTIONS);
I'm seeing segments created
in the temp tablespace (from v$sort_usage)
Sess# User Name
TABLESPACE
CONTENTS
ext BLOCKS
SZ_MB - - --- -
-- -- 9
SYS
TEMP2
TEMPORARY 1629
208512 1629
11
SYS
TEMP2
TEMPORARY 1629
208512 1629
12
SYS
TEMP2
TEMPORARY 1629
208512 1629
13
SYS
TEMP2
TEMPORARY 1629
208512 1629
I've enough space and it'll
finish by tomorrow morning which is the deadline but does anyone know
why TEMP is required and what the end to end process
is?
I would have expected to
have seen temporary segments created, but in the partitions own
tablespace. It doesnt se
Re: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace
You're welcome, please let me know the
results.
It was news to me as well, that so much temp space
would be used in your type of parallel direct load insert...
Btw, you can use nologging hint as well in your DML
if you haven't already altered the target table to nologging.
Tanel.
- Original Message -
From:
Turner,
Adrian A SITI-ITPSIE
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 17, 2003 4:14
PM
Subject: RE: Nosort parallel dml uses
TEMP tablespace
Thanks for having a look Tanel; that makes great
sense.
I'll
have a quick play to convince myself but I'd say its a lesson
learned.
BTW
did you too experience more temp allocation than you would have
expected?
Thanks again,
Adrian
-Original Message-From: Tanel Poder
[mailto:[EMAIL PROTECTED]Sent: 17 September 2003
14:00To: Multiple recipients of list ORACLE-LSubject:
Re: Nosort parallel dml uses TEMP tablespace
Hi!
Btw, I did a little testing with PARALLEL 2,
and yes it seems to be a parallel slave communications issue, despite the
segments show type "SORT".
When i traced sorting with 10032 10033, I
saw sort segment allocation, but no real sorting occurring. When I compared
first rows in source table with target one,I saw that when in source
table I had rows (1,2,3,4,5,6,7), then in beginning of target table, I had
(1,3,5,7,etc..). That means I had only one session reading data (query
coordinator), which then evenly distributed data to it's slaves.
You should includePARALLEL hint to your
select statement as well, with same number of slaves (in addition to insert
+append one), that way PX can work parallel_to_parallel way, thus for each
producing (query) slave there is a consuming slave and data doesn't have to
be distributed or splitted from one source to several queues (like with
parallel_from_serial). That way no data is intermediately stored to
temporary segments.
This worked for me, but since PX is a complex
mechanism, youmight see different results.
Tanel.
- Original Message -
From:
Tanel Poder
To: Multiple recipients of list
ORACLE-L
Sent: Wednesday, September 17, 2003
2:44 PM
Subject: Re: Nosort parallel dml uses
TEMP tablespace
Hi!
It could be that parallel slaves are passing
intermediate results using TEMP tablespace for some reason. (When table
queues are full in some cases or smth like that). It can depend on
parallel execution plan as well (whether it's parallel_to_parallel or
parallel_to_serial data distribution).
If you used only /*+ APPEND */ without
parallel, then operations were done serially by one session only, thus no
intermediate result passing between slaves (using table queues) were
needed.
You might want to post your question to
comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering
more advanced performance related questions there.
Tanel.
- Original Message -
From:
Turner, Adrian A SITI-ITPSIE
To: Multiple recipients of list
ORACLE-L
Sent: Wednesday, September 17, 2003
11:39 AM
Subject: RE: Nosort parallel dml
uses TEMP tablespace
I'm even more confused now.
TEMP ran out of space at 20GB -
I'm only inserting 12.2GB!
So I kicked it off again with
the hint just as /*+ APPEND */ and it completed successfully in 1/6th
the time to failure of the parallel insert.
-Original
Message- From: Turner, Adrian
A SITI-ITPSIE Sent: 16 September 2003 15:16 To: Multiple recipients of list ORACLE-L Subject:
Nosort parallel dml uses TEMP
tablespace
Apologies if this has been
covered before but metalink is not clear on the reasons behind
it
The database is running
version 9204 EE on WinNT Sp6; and the statement is a parallel direct
load into partitioned table selecting from a 12.2GB source
table.
ALTER SESSION
ENABLE PARALLEL DML ;
INSERT /*+
APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO
TRANSACTION_NEW (select * from
TRANSACTIONS);
I'm seeing segments
created in the temp tablespace (from v$sort_usage)
Sess# User Name
TABLESPACE
CONTENTS
ext BLOCKS
Nosort parallel dml uses TEMP tablespace
Title: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
Locally-managed temp tablespace question
Oracle 8173 on Solaris 2.8: bash-2.03$ pwd /oracle/u02/oradata/YPD bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 45549803 2292492767%/oracle/u02 bash-2.03$ ls -l templmt* -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:40 templmt01.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt02.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt03.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt04.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt05.dbf bash-2.03$ rm templmt* bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 44244651 2423007965%/oracle/u02 I thought I should get 10GB (2097160192 x 5) back, but instead I got only 1.3MB ( 24230079 - 22924927) released. Could someone explain? BTW, when I created the temp files (templmt0[1-5]) for locally-managed temp tablespace, I found they were created extremely fast, so I suspected that not real disk space were allocated. But the ls -l command still showed 10GB were allocated. TIA. Guang Mei [EMAIL PROTECTED] http://www.geocities.com/guangmei/ _ STOP MORE SPAM with the new MSN 8 and get 3 months FREE*. http://join.msn.com/?page=features/junkmailxAPID=42PS=47575PI=7324DI=7474SU= http://www.hotmail.msn.com/cgi-bin/getmsgHL=1216hotmailtaglines_stopmorespam_3mf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [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: Locally-managed temp tablespace question
Yes, this weird sizing with Tempfile...strange isn't it... ls shows accurately.. On HPUX the bdf (or df in your case) doesn't register the full size that is indicated what is supposed to be used by the tempfile. We haven't found a solution for this yet.. we cant wait till a directory fills and someone puts a datafile in the directory so the tempfile is reduced. Apparently I have been told by others that only space will shrink and you wont corrupt the files... but I dont like it. I just updated my scripts to show the tempfiles separately so I have a warning before I do any space change in the middle of the night. Note: I think although rm'ing the files..it seems to take unix a while to catch up and report its gone... I have had that several times. It creates fast..which is nice...since you have to do it manually if you do a restore with RMAN. A nice gotcha. Brian Spears -Original Message- Sent: Friday, December 20, 2002 12:05 PM To: Multiple recipients of list ORACLE-L Oracle 8173 on Solaris 2.8: bash-2.03$ pwd /oracle/u02/oradata/YPD bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 45549803 2292492767%/oracle/u02 bash-2.03$ ls -l templmt* -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:40 templmt01.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt02.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt03.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt04.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt05.dbf bash-2.03$ rm templmt* bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 44244651 2423007965%/oracle/u02 I thought I should get 10GB (2097160192 x 5) back, but instead I got only 1.3MB ( 24230079 - 22924927) released. Could someone explain? BTW, when I created the temp files (templmt0[1-5]) for locally-managed temp tablespace, I found they were created extremely fast, so I suspected that not real disk space were allocated. But the ls -l command still showed 10GB were allocated. TIA. Guang Mei [EMAIL PROTECTED] http://www.geocities.com/guangmei/ _ STOP MORE SPAM with the new MSN 8 and get 3 months FREE*. http://join.msn.com/?page=features/junkmailxAPID=42PS=47575PI=7324DI=747 4SU= http://www.hotmail.msn.com/cgi-bin/getmsgHL=1216hotmailtaglines_stopmorespa m_3mf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Spears, Brian INET: [EMAIL PROTECTED] 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: [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: Locally-managed temp tablespace question
They are sparse ie the space is not allocated until required. On some OS's you can workaround it by copying the file elsewhere and then copying it back. hth connor --- Guang Mei [EMAIL PROTECTED] wrote: Oracle 8173 on Solaris 2.8: bash-2.03$ pwd /oracle/u02/oradata/YPD bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 45549803 22924927 67%/oracle/u02 bash-2.03$ ls -l templmt* -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:40 templmt01.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt02.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt03.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt04.dbf -rw-r- 1 oracle oinstall 2097160192 Dec 19 20:27 templmt05.dbf bash-2.03$ rm templmt* bash-2.03$ df -k|grep /oracle/u02 /dev/dsk/c1t2d0s070592505 44244651 24230079 65%/oracle/u02 I thought I should get 10GB (2097160192 x 5) back, but instead I got only 1.3MB ( 24230079 - 22924927) released. Could someone explain? BTW, when I created the temp files (templmt0[1-5]) for locally-managed temp tablespace, I found they were created extremely fast, so I suspected that not real disk space were allocated. But the ls -l command still showed 10GB were allocated. TIA. Guang Mei [EMAIL PROTECTED] http://www.geocities.com/guangmei/ _ STOP MORE SPAM with the new MSN 8 and get 3 months FREE*. http://join.msn.com/?page=features/junkmailxAPID=42PS=47575PI=7324DI=7474SU= http://www.hotmail.msn.com/cgi-bin/getmsgHL=1216hotmailtaglines_stopmorespam_3mf -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ 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.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [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).
TEMP/RBS LMTs (was: Convert TEMP tablespace...)
10M temp TS? You must have quite a nicely controlled working environment, Jared! ;) But your example does bring up a question: For TEMP and RBS LMTs, does the Goldilocks Rule (128K/4M/128M uniform extents, post version 7.x) follow? I had created my TEMP and RBS with 1MB uniform before knowing about Goldilocks and the infamous How to Stop Defragmenting and Start Living white paper. And that paper is a little vague about TEMP and RBS LMTs, other than to say that there should be between 1024 and 4096 extents in each. Thoughts? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:Jared.Still;radisys.com] Sent: Wednesday, November 06, 2002 3:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: Convert TEMP tablespace from datafiles to tempfiles create temporary tablespace temp2 tempfile '/u01/oradata/dv01/temp2.dbf' size 10m extent management local uniform size 128k; HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
Jesse, I did the same thing last week on our sandbox system using the method others have prescribed. There is a note 140913.1 covering a LMT bug in 8.1.6. under OpenVMS. You might want to double check to make sure no equivalent problem exists on your platform. Ron, As Jared pointed out, it's the Temporary tablespace (not a tablespace with temporary contents) that permits local extent management. In 8.1.7 I'm sure you've already tried: create tablespace temp_contents datafile '/oracle/.../temp.data1' size 128M temporary extent management local uniform size 4M; or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7 will let you assign a Permanent LMT as temporary_tablespace for a user, but won't let you create temporary segments there. Mike -Original Message- Sent: Wednesday, November 06, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] 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: [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/RBS LMTs (was: Convert TEMP tablespace...)
10M? Hardly. That was just a test script to make sure the syntax was correct. 10m just happens to be created very quickly. The sort_area_size equal or be a multiple of your TEMP extent size. Jared On Thursday 07 November 2002 09:18, Jesse, Rich wrote: 10M temp TS? You must have quite a nicely controlled working environment, Jared! ;) But your example does bring up a question: For TEMP and RBS LMTs, does the Goldilocks Rule (128K/4M/128M uniform extents, post version 7.x) follow? I had created my TEMP and RBS with 1MB uniform before knowing about Goldilocks and the infamous How to Stop Defragmenting and Start Living white paper. And that paper is a little vague about TEMP and RBS LMTs, other than to say that there should be between 1024 and 4096 extents in each. Thoughts? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: [EMAIL PROTECTED] [mailto:Jared.Still;radisys.com] Sent: Wednesday, November 06, 2002 3:35 PM To: Multiple recipients of list ORACLE-L Subject: Re: Convert TEMP tablespace from datafiles to tempfiles create temporary tablespace temp2 tempfile '/u01/oradata/dv01/temp2.dbf' size 10m extent management local uniform size 128k; HTH Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
Mike, Perhaps the reason that I was confused is that I did not phrase the question correctly. If you create a tablespace to be used as the temporary tablespace for users and create it as type temporary ( segments used by implicit sorts to handle order by clause) you can not use the LMT clauses. If you create a temporary tablespace that is only in existence for the duration of the session you can use the LMT clauses. Does this mean that you have to recreate the temporary tablespace after a reboot? Another point about LMT's. I like them and use them in the majority of my tablespaces. When you choose the uniform size it can be what ever you choose to work comfortably with the data size of the table. If I remember correctly( working through the gray hair again) a temporary extent should be equall to or a multiple of the sort area size so it does not create unnecessary thrashing when trying to fit a sort into the temp. Am I correct in what I remember? THanks. Ron [EMAIL PROTECTED] 11/07/02 12:53PM Jesse, I did the same thing last week on our sandbox system using the method others have prescribed. There is a note 140913.1 covering a LMT bug in 8.1.6. under OpenVMS. You might want to double check to make sure no equivalent problem exists on your platform. Ron, As Jared pointed out, it's the Temporary tablespace (not a tablespace with temporary contents) that permits local extent management. In 8.1.7 I'm sure you've already tried: create tablespace temp_contents datafile '/oracle/.../temp.data1' size 128M temporary extent management local uniform size 4M; or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7 will let you assign a Permanent LMT as temporary_tablespace for a user, but won't let you create temporary segments there. Mike -Original Message- Sent: Wednesday, November 06, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [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/RBS LMTs (was: Convert TEMP tablespace...)
Does the SORT_AREA_SIZE not being a multiple of TEMP extent size have that much impact if disk sorts are only 0.03% (3/100ths of 1 percent) of total sorts? My numbers are according to V$SYSSTAT. What resource is affected? Disk? Memory? CPU? Beer? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jared Still [mailto:jkstill;cybcon.com] Sent: Thursday, November 07, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Subject: Re: TEMP/RBS LMTs (was: Convert TEMP tablespace...) 10M? Hardly. That was just a test script to make sure the syntax was correct. 10m just happens to be created very quickly. The sort_area_size equal or be a multiple of your TEMP extent size. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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/RBS LMTs (was: Convert TEMP tablespace...)
In that case, I wouldn't worry about it. :) On Thursday 07 November 2002 14:19, Jesse, Rich wrote: Does the SORT_AREA_SIZE not being a multiple of TEMP extent size have that much impact if disk sorts are only 0.03% (3/100ths of 1 percent) of total sorts? My numbers are according to V$SYSSTAT. What resource is affected? Disk? Memory? CPU? Beer? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jared Still [mailto:jkstill;cybcon.com] Sent: Thursday, November 07, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Subject: Re: TEMP/RBS LMTs (was: Convert TEMP tablespace...) 10M? Hardly. That was just a test script to make sure the syntax was correct. 10m just happens to be created very quickly. The sort_area_size equal or be a multiple of your TEMP extent size. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
Ron, you're confusing me. Perhaps the reason that I was confused is that I did not phrase the question correctly. If you create a tablespace to be used as the temporary tablespace for users and create it as type temporary ( segments used by implicit sorts to handle order by clause) you can not use the LMT clauses. That comes as a surprise, as I have several databases setup that way. If you create a temporary tablespace that is only in existence for the duration of the session you can use the LMT clauses. Does this mean that you have to recreate the temporary tablespace after a reboot? What kind of tablespace are you referring to? I don't understand whay you mean by 'duration of the session' in context of a tablespace. Jared Another point about LMT's. I like them and use them in the majority of my tablespaces. When you choose the uniform size it can be what ever you choose to work comfortably with the data size of the table. If I remember correctly( working through the gray hair again) a temporary extent should be equall to or a multiple of the sort area size so it does not create unnecessary thrashing when trying to fit a sort into the temp. Am I correct in what I remember? THanks. Ron [EMAIL PROTECTED] 11/07/02 12:53PM Jesse, I did the same thing last week on our sandbox system using the method others have prescribed. There is a note 140913.1 covering a LMT bug in 8.1.6. under OpenVMS. You might want to double check to make sure no equivalent problem exists on your platform. Ron, As Jared pointed out, it's the Temporary tablespace (not a tablespace with temporary contents) that permits local extent management. In 8.1.7 I'm sure you've already tried: create tablespace temp_contents datafile '/oracle/.../temp.data1' size 128M temporary extent management local uniform size 4M; or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7 will let you assign a Permanent LMT as temporary_tablespace for a user, but won't let you create temporary segments there. Mike -Original Message- Sent: Wednesday, November 06, 2002 10:56 AM To: Multiple recipients of list ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [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).
Convert TEMP tablespace from datafiles to tempfiles
Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
Rich,
I have a better set of steps for you:
1). Create a new tablespace (TEMPLMT) and make it a LMT.
2). One by one, issue ALTER USER {username} TEMPORARY TABLESPACE TEMPLMT;
3). Bounce the database
4). DROP TABLESPACE TEMP INCLUDING CONTENTS;
5). Backup the database
6). Let users at it.
7). Go have a beer.
The only negative here is that your TEMP tablespace is called TEMPLMT, but
it just doesn't matter. If you want, you can do it again, this time
creating the tablespace as TEMP if it makes you feel better.
Your method would work just fine. Not sure if the users would lose the
designation of their TEMPORARY TABLESPACE, but if they do, you can easily
create a script to set their temp tablespace to TEMP again.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, November 06, 2002 10:56 AM
To: Multiple recipients of list ORACLE-L
Hey all,
I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to
take the opportunity to convert the datafiles of the TEMP tablespace to
tempfiles. My reason for this change is primarily to get the TEMP
tablespace LMT, but also to shrink our hotbacks w/o modifying the working
script.
I've been trying to reason out this task in my head, as I can't find much on
MetaLink, and here's what I've got so far:
1) Shutdown DB.
2) Backup DB.
3) Startup restricted.
4) Offline tablespace TEMP.
5) Drop tablespace TEMP.
6) Create new temporary TEMP LMT.
7) Bounce instance.
I don't yet have an arena to try this in. Will users whose assigned
TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments
on the procedure?
TIA!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [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: Convert TEMP tablespace from datafiles to tempfiles
Rich, If you've got the disk space, do it while the DB is up. Much simpler. Create a new Temporary LMT - call it NewTemp - with the appropriate tempfiles. Switch everyone to NewTemp by spooling and executing this and running the spooled file: Select 'Alter User ' || UserName || ' Temporary Tablespace NewTemp ;' From DBA_Users; After a day or two, or when you can determine that no one is using the old Temp tablespace, offline and drop Temp. If you've just got to have the Temp tablespace named Temp, repeat the above steps to create a new Temp LMT. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Convert TEMP tablespace from m datafiles to tempfiles 11/06/2002 09:56 AM Please respond to ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
If have the space, create the new TEMP tablespace first, alter all users that have the old one to now use the new one, drop the old tablespace. Jared Jesse, Rich [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 07:56 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Convert TEMP tablespace from datafiles to tempfiles Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
List, Would you please explain to me how you are able to get TEMP and LMT combined together. I use 8.1.7 and it is not allowed. Oracle8i DBA Handbook Loney and Theriault, Osborne Oracle Press, Page 598 NOTE If you specify Local in a create tablespace command, you can not specify a default storage clause, minextents, or temporary. If you use the create temporary tablespace command to create the tablespace, you can specify extent_management local. The tablespace that is being created is a tablespace with type = temporary not permanent? correct? Ron ROR mô¿ôm [EMAIL PROTECTED] 11/06/02 01:39PM Rich, If you've got the disk space, do it while the DB is up. Much simpler. Create a new Temporary LMT - call it NewTemp - with the appropriate tempfiles. Switch everyone to NewTemp by spooling and executing this and running the spooled file: Select 'Alter User ' || UserName || ' Temporary Tablespace NewTemp ;' From DBA_Users; After a day or two, or when you can determine that no one is using the old Temp tablespace, offline and drop Temp. If you've just got to have the Temp tablespace named Temp, repeat the above steps to create a new Temp LMT. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Convert TEMP tablespace from m datafiles to tempfiles 11/06/2002 09:56 AM Please respond to ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [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: Convert TEMP tablespace from datafiles to tempfiles
Yeah, from the List, this method seems to be preferred. My only problem is
in step 7 -- change a to several. This could require more testing,
however. I imagine I'll be trying to ATLER TABLESPAVE TMEP afterwards...
Thx all! :)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
From: Mercadante, Thomas F [mailto:NDATFM;labor.state.ny.us]
Sent: Wednesday, November 06, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Convert TEMP tablespace from datafiles to tempfiles
Rich,
I have a better set of steps for you:
1). Create a new tablespace (TEMPLMT) and make it a LMT.
2). One by one, issue ALTER USER {username} TEMPORARY
TABLESPACE TEMPLMT;
3). Bounce the database
4). DROP TABLESPACE TEMP INCLUDING CONTENTS;
5). Backup the database
6). Let users at it.
7). Go have a beer.
The only negative here is that your TEMP tablespace is
called TEMPLMT, but
it just doesn't matter. If you want, you can do it again, this time
creating the tablespace as TEMP if it makes you feel better.
Your method would work just fine. Not sure if the users
would lose the
designation of their TEMPORARY TABLESPACE, but if they do,
you can easily
create a script to set their temp tablespace to TEMP again.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [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: Convert TEMP tablespace from datafiles to tempfiles
create temporary tablespace temp2 tempfile '/u01/oradata/dv01/temp2.dbf' size 10m extent management local uniform size 128k; HTH Jared Ron Rogers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2002 12:10 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Convert TEMP tablespace from datafiles to tempfiles List, Would you please explain to me how you are able to get TEMP and LMT combined together. I use 8.1.7 and it is not allowed. Oracle8i DBA Handbook Loney and Theriault, Osborne Oracle Press, Page 598 NOTE If you specify Local in a create tablespace command, you can not specify a default storage clause, minextents, or temporary. If you use the create temporary tablespace command to create the tablespace, you can specify extent_management local. The tablespace that is being created is a tablespace with type = temporary not permanent? correct? Ron ROR mô¿ôm [EMAIL PROTECTED] 11/06/02 01:39PM Rich, If you've got the disk space, do it while the DB is up. Much simpler. Create a new Temporary LMT - call it NewTemp - with the appropriate tempfiles. Switch everyone to NewTemp by spooling and executing this and running the spooled file: Select 'Alter User ' || UserName || ' Temporary Tablespace NewTemp ;' From DBA_Users; After a day or two, or when you can determine that no one is using the old Temp tablespace, offline and drop Temp. If you've just got to have the Temp tablespace named Temp, repeat the above steps to create a new Temp LMT. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jesse, Rich Rich.Jesse@qti To: Multiple recipients of list ORACLE-L world.com [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Convert TEMP tablespace from m datafiles to tempfiles 11/06/2002 09:56 AM Please respond to ORACLE-L Hey all, I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd like to take the opportunity to convert the datafiles of the TEMP tablespace to tempfiles. My reason for this change is primarily to get the TEMP tablespace LMT, but also to shrink our hotbacks w/o modifying the working script. I've been trying to reason out this task in my head, as I can't find much on MetaLink, and here's what I've got so far: 1) Shutdown DB. 2) Backup DB. 3) Startup restricted. 4) Offline tablespace TEMP. 5) Drop tablespace TEMP. 6) Create new temporary TEMP LMT. 7) Bounce instance. I don't yet have an arena to try this in. Will users whose assigned TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any comments on the procedure? TIA! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE
Dictionary managed Temp tablespace space missing
Hello all, DB: 8172 I have a dictionary managed temp tablespace, size 22 Gig. When I see the free space in dba_free_space, it is showing only 18 Gig as free. But there is no usage found in v$sort_usage, v$sort_segment. I bounced the DB to reclaim to free-up TEMP. But no use. Its free space is still 18 Gig in dba_free_space. (There is no objects created in this tablespace, checked in dba_segments and this TS type is TEMPORARY) am I missing something to calculate usage of TEMP. any clues ? Thanks and regards, Srinivas __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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).
TEMP TABLESPACE
Hi List, I have two data files for my TEMP tablespace, i just find out one of the data file AUTO EXTEND is set to on is it right?? I beleive for TEMP tablespace autoextend shoudn't be ON, AM I RIGHT or NOT? Now my data file almost full for TEMP tablespace how can I Clean it up, I run ALTER TABLESPACE TEMP COALESCE BUT STILL DATA FILE IS FULL ORACLE 8.1.7.0 ON SUN SOLARIS \THANKS ALLOT Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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 TABLESPACE
Hamid, 1) Do not let TEMP tablespace have autoextend set to on - my .02 is to never have this set to on for any tablespace, but to monitor and expand as necessary. Other DBAs like autoextend, so you may want that for other tablespaces, but not for TEMP. 2) A nice trick to get TEMP cleared of allocated-but-no-longer-used space is to set the next extent to, say 512MB (from say, 1024MB). This will cause all space but what is being currently used to be wiped out. The set next extent back to 1024 (or whatever it was before). Thank you, Paul Sherman DBAElcom, Inc. email - [EMAIL PROTECTED] -Original Message- Sent: Monday, May 20, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Hi List, I have two data files for my TEMP tablespace, i just find out one of the data file AUTO EXTEND is set to on is it right?? I beleive for TEMP tablespace autoextend shoudn't be ON, AM I RIGHT or NOT? Now my data file almost full for TEMP tablespace how can I Clean it up, I run ALTER TABLESPACE TEMP COALESCE BUT STILL DATA FILE IS FULL ORACLE 8.1.7.0 ON SUN SOLARIS \THANKS ALLOT Hamid Alavi Office 818 737-0526 Cell818 402-1987 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Paul R. 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).
TEMP tablespace growing abnormally -Sort Area
Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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).
TEMP tablespace growing abnormally
Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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 tablespace growing abnormally -Sort Area
Is your TEMP tablespace set to TEMPORARY or PERMANENT? -Original Message- Sent: Tuesday, May 14, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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 tablespace growing abnormally
Do you have auto extend on the temp tablespace? Bryan -Original Message- Sent: Tuesday, May 14, 2002 2:10 PM To: Multiple recipients of list ORACLE-L Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan 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 tablespace growing abnormally -Sort Area
Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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 tablespace growing abnormally -Sort Area
I think you meant 8MB not 8GB, right? Take a look at v$sort_usage and find these sessions that are filling the TEMP TS. From there find the sql behind this temp space and try to figure out if it's normal to allocate this space or there is a CARTESIAN join for example that is giving you this problem. It's not weired to fill a 32 GB temp TS in a data warehouse application. Regards, Waleed -Original Message- Sent: Tuesday, May 14, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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 tablespace growing abnormally
Yes, But this shouldn't warrant 'indefinite extension' of the TB ?!? -Original Message- Sent: Tuesday, May 14, 2002 9:39 PM To: Multiple recipients of list ORACLE-L Do you have auto extend on the temp tablespace? Bryan -Original Message- Sent: Tuesday, May 14, 2002 2:10 PM To: Multiple recipients of list ORACLE-L Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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 tablespace growing abnormally -Sort Area
TEMPORARY -Original Message- Sent: Tuesday, May 14, 2002 9:39 PM To: Multiple recipients of list ORACLE-L Is your TEMP tablespace set to TEMPORARY or PERMANENT? -Original Message- Sent: Tuesday, May 14, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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 tablespace growing abnormally -Sort Area
Dennis, I haven't seen Tim's posting !!? as regards my woos -Original Message- Sent: Tuesday, May 14, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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 tablespace growing abnormally
Simon, If auto extend is on the tablespace and there is a runaway or resource intensive process that is using up a great amount of temp space, Oracle will keep expanding the size of the tablespace until you have run out of space on the disk. Bryan -Original Message- Sent: Tuesday, May 14, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Yes, But this shouldn't warrant 'indefinite extension' of the TB ?!? -Original Message- Sent: Tuesday, May 14, 2002 9:39 PM To: Multiple recipients of list ORACLE-L Do you have auto extend on the temp tablespace? Bryan -Original Message- Sent: Tuesday, May 14, 2002 2:10 PM To: Multiple recipients of list ORACLE-L Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan 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 tablespace growing abnormally -Sort Area
Simon - My apologies. Tim's response was for the other problem that is getting a lot of responses. In trying to read the postings as well as get some work done here, I confused the two. I was thinking that the response said that the loader first loads into the TEMPORARY segment, then merged with the table segment. I confused the two since they both involved temp. I don't have a specific solution to your problem, but my survival instincts as a crusty old DBA (but still employed) say don't make your TEMP tablespace autoextend. It just feels like you are trolling for trouble. I am a big fan of autoextend and have it on all my datafiles except for a couple, and guess what -- TEMP is not on autoextend for me. Maybe once you lick this one, you can give talks worldwide on either a) why you should never let TEMP autoextend or b) why you're missing the best part of Oracle by not letting your TEMP autoextend. I promise I'll attend your talk. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Dennis, I haven't seen Tim's posting !!? as regards my woos -Original Message- Sent: Tuesday, May 14, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB - 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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
Large record count in fet$ for TEMP tablespace
When trying to select * from DBA_FREE_SPACE_COALESCED the query hangs. It also hangs when trying to calculate free space from the DBA_FREE_SPACE view. I looked in the sys.fet$ table and found that the TEMP tablespace has 21423 entries and I believe this is my problem. Now for my questions. 1. Is coalescing the best solution for this problem? 1. Can I coalesce the tablespace without changing it to a permanent tablespace? 2. Is there a faster way to drop the exents then the alter tablespace coalesce command? I ran it for about 10 minutes and canceled it. 3. Is this normal and do I need to coalesce the TEMP tablespace on a regular basis to avoid this happening again? Thanks,CAJ
Re: Large record count in fet$ for TEMP tablespace
I've seen some hacks in the past involving dropping all segments for that tspace, direct deletes from fet$ and then dropping the tablespace... Not for the faint hearted, certainly not supported and you never heard it from me :-) Cheers Connor --- Casey A. Jordan [EMAIL PROTECTED] wrote: When trying to select * from DBA_FREE_SPACE_COALESCED the query hangs. It also hangs when trying to calculate free space from the DBA_FREE_SPACE view. I looked in the sys.fet$ table and found that the TEMP tablespace has 21423 entries and I believe this is my problem. Now for my questions. 1. Is coalescing the best solution for this problem? 1. Can I coalesce the tablespace without changing it to a permanent tablespace? 2. Is there a faster way to drop the exents then the alter tablespace coalesce command? I ran it for about 10 minutes and canceled it. 3. Is this normal and do I need to coalesce the TEMP tablespace on a regular basis to avoid this happening again? Thanks, CAJ = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ 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?Connor=20McDonald?= 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: Large record count in fet$ for TEMP tablespace
Been there... Done that. After we estimated coalescing free space would take 10 hours. drop all the objects in the tablespace delete from fet$ for that tablespace add a single row in fet$ for the entire tablespace drop the tablespace (reduced from 10 hours to couple of seconds). recreate the tablespace. DISCLAIMER: I do not warrant the accuracy, adequacy or completeness of this information. In no event, will I be liable for any damages, direct or indirect, losses, expenses, or outages, or system failure. If you are brave and helpless enough, to do the things I did, it's at your own risk. And for the answer to question 3), this is not normal. You should never let any tablespace run into this many number of free extents. Unless you want to start defragmenting, and stop living ;-) http://www.dbatoolbox.com/WP2001/spacemgmt/defrag.htm Regards Raj Connor McDonald To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hamcdc@yahoocc: .co.uk Subject: Re: Large record count in fet$ for TEMP tablespace Sent by: root@fatcity. com March 19, 2002 12:08 PM Please respond to ORACLE-L I've seen some hacks in the past involving dropping all segments for that tspace, direct deletes from fet$ and then dropping the tablespace... Not for the faint hearted, certainly not supported and you never heard it from me :-) Cheers Connor --- Casey A. Jordan [EMAIL PROTECTED] wrote: When trying to select * from DBA_FREE_SPACE_COALESCED the query hangs. It also hangs when trying to calculate free space from the DBA_FREE_SPACE view. I looked in the sys.fet$ table and found that the TEMP tablespace has 21423 entries and I believe this is my problem. Now for my questions. 1. Is coalescing the best solution for this problem? 1. Can I coalesce the tablespace without changing it to a permanent tablespace? 2. Is there a faster way to drop the exents then the alter tablespace coalesce command? I ran it for about 10 minutes and canceled it. 3. Is this normal and do I need to coalesce the TEMP tablespace on a regular basis to avoid this happening again? Thanks, CAJ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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:Hot backup and TEMP tablespace
ALWAYS BACKUP THE ROLLBACK TABLESPACE! or you will not be able to recover. How else will Oracle rollback a transaction in the works when the database went down. I do not back up TEMP or my INDEX tables spaces. I have scripts that recreate all of these. (150GB of indices, not worth the tape.) And while I use RMAN for backups I do all my recoveries from the our scripted hot backups. I also practise recoveries every 6 months. RMAN backups fine, it's recoveries it has trouble with :) Why? There is limited flexibility with RMAN along with an added dependency. As most know I loathe unecessary dependencies. An Example: power surge blows out Machine and hub/router power supply. Machine automagicaly fails over to alternate power, however, the hub/router needs servicing. Your RMAN catalog is on the other side of the hub/router. What are you going to do now? And yes, as I keep saying, I am paranoid. Dave Tom wrote: steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also. Why not INDEX tablespaces - heck, if you have the scripts, these could be re-created too! -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan 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: Hot backup and TEMP tablespace
Traci, Yes, you can skip backing up the TEMP tablespaces, and as Kirti suggested, and have instructions in your recovery manual on how to rebuild the tablespace when you are going thru a recovery. In my humble opinion, I would not do this. It's kinda like using duct tape to cover a crack in the window - it works, but its really not right. Do you really want to be in the position of performing extra recovery steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also. Why not INDEX tablespaces - heck, if you have the scripts, these could be re-created too! My point (as a professional DBA) is that, backups should be intact so that you can recover easily without having to do extra work. It is really one less thing to remember and have to worry about. If your real issue is that you cannot perform your backup in the time allotted, or that you need more disk space to perform your backup, you should either investigate Rman (as Jared suggested), or buy more disk - both should be considered the cost of doing business. I am a strong proponent of Rman - it solves MANY backup and recovery problems. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, January 26, 2002 10:40 AM To: Multiple recipients of list ORACLE-L I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Hot backup and TEMP tablespace
If you are using a temp tablespace with a temp file you really are not suppose to back it up. This comes from Oracle, not folks trying to save time. You cannot put them into backup mode so how do you recommend backing them up? -Original Message- Thomas F Sent: Monday, January 28, 2002 5:31 AM To: Multiple recipients of list ORACLE-L Traci, Yes, you can skip backing up the TEMP tablespaces, and as Kirti suggested, and have instructions in your recovery manual on how to rebuild the tablespace when you are going thru a recovery. In my humble opinion, I would not do this. It's kinda like using duct tape to cover a crack in the window - it works, but its really not right. Do you really want to be in the position of performing extra recovery steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also. Why not INDEX tablespaces - heck, if you have the scripts, these could be re-created too! My point (as a professional DBA) is that, backups should be intact so that you can recover easily without having to do extra work. It is really one less thing to remember and have to worry about. If your real issue is that you cannot perform your backup in the time allotted, or that you need more disk space to perform your backup, you should either investigate Rman (as Jared suggested), or buy more disk - both should be considered the cost of doing business. I am a strong proponent of Rman - it solves MANY backup and recovery problems. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, January 26, 2002 10:40 AM To: Multiple recipients of list ORACLE-L I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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: Hot backup and TEMP tablespace
On Mon, 28 Jan 2002, Mercadante, Thomas F wrote: Do you really want to be in the position of performing extra recovery steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also... You can't skip the tablespaces containing active rollback segments. You need them to roll back transactions that were uncommitted as of the time up to which you want to recover your database. My point (as a professional DBA) is that, backups should be intact so that you can recover easily without having to do extra work. It is really one less thing to remember and have to worry about. If well documented and/or automated, I say that it is a legitimate way to save resources, especially if there is a very large temporary tablespace, especially if the majority of users are not big sorters. You could create a small (1Gb) temp tablespace right after opening the recovered dtabase, then assign all users to it. Meanwhile, you can create a large temporary tablespace and assign any large sorters to that when it gets done. I think skipping TEMPORARY tablespaces is a good idea if you iron out the recovery steps. It really isn't complicated or much extra work. You also mentioned skipping indexes in your backups, as though it were an unreasonable thing to do. I think that's a good idea too, if someone deems it appropriate and managable. I guess you'd have to dump the DDL for all indexes with every backup, and you'd have to veryify it is actually faster to rebuild them than to restore. RMAN solves problems like disk space and backing up free space, and is a great utility. But is the cost of implementation worth it, if the original poster is going to successfully buy a couple years with the existing system, just by skipping TEMP? -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Hot backup and TEMP tablespace
Kimberly, That is an excellent point! But the original writer was taking a hot backup of the TEMP tablespace, so she must be using an older version of Oracle. Maybe she should migrate to 8i? :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, January 28, 2002 10:16 AM To: Multiple recipients of list ORACLE-L If you are using a temp tablespace with a temp file you really are not suppose to back it up. This comes from Oracle, not folks trying to save time. You cannot put them into backup mode so how do you recommend backing them up? -Original Message- Thomas F Sent: Monday, January 28, 2002 5:31 AM To: Multiple recipients of list ORACLE-L Traci, Yes, you can skip backing up the TEMP tablespaces, and as Kirti suggested, and have instructions in your recovery manual on how to rebuild the tablespace when you are going thru a recovery. In my humble opinion, I would not do this. It's kinda like using duct tape to cover a crack in the window - it works, but its really not right. Do you really want to be in the position of performing extra recovery steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also. Why not INDEX tablespaces - heck, if you have the scripts, these could be re-created too! My point (as a professional DBA) is that, backups should be intact so that you can recover easily without having to do extra work. It is really one less thing to remember and have to worry about. If your real issue is that you cannot perform your backup in the time allotted, or that you need more disk space to perform your backup, you should either investigate Rman (as Jared suggested), or buy more disk - both should be considered the cost of doing business. I am a strong proponent of Rman - it solves MANY backup and recovery problems. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, January 26, 2002 10:40 AM To: Multiple recipients of list ORACLE-L I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante
RE: Hot backup and TEMP tablespace
Hi Traci, You need to keep the following in mind before deciding to stop backing up TEMP. There are two kinds of media recovery : Complete and incomplete. The former requires that the database be mounted but not open when recovery is being performed. If the database was just mounted, then you will not be able to drop the TEMP tablespace. Add to this the fact that Oracle applies archive logs based on the file header SCNs. If you restore an old copy of the TEMP tablespace's datafiles, the recovery will require that *all* the archive logs since the last backup of TEMP. This implies that you need to be able to get these archive logs from the backup (without missing even one), and spend time in applying them. This is something you DON'T want to do in an emergen I would look at using RMAN as Jared has suggested. Empty blocks are not backed up, and there are opportunties for incremental backups as well. We have a 13 Gb TEMP tablespace on our Production APPS 10.7 database and back that up everyday. Tape is cheap - time is not. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Fear is the darkroom where Evil develops your negatives. Wanna break free of fear? Click on 'http://www.needhim.org' ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 26, 2002 7:40 AM To: Multiple recipients of list ORACLE-L Subject: Hot backup and TEMP tablespace I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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: Hot backup and TEMP tablespace
On Mon, 28 Jan 2002, John Kanagaraj wrote: There are two kinds of media recovery : Complete and incomplete. The former requires that the database be mounted but not open when recovery is being performed. If the database was just mounted, then you will not be able to drop the TEMP tablespace. She is talking about offline drop of the datafiles comprising the temporary tablespaces before you begin the recovery. You can do this in mount mode regardless of what type of recovery you are performing. You wait to drop and recreate the tablespace until *after* you are done recovering (complete or incomplete) and have opened the database. The existence of the tablespace in the dictionary has no effect on recovery. Add to this the fact that Oracle applies archive logs based on the file header SCNs. If you restore an old copy of the TEMP tablespace's datafiles, the recovery will require that *all* the archive logs since the last backup of TEMP. This implies that you need to be able to get these archive logs from the backup (without missing even one), and spend time in applying them. This is something you DON'T want to do in an emergen She is not planning to restore an old copy of the temp datafiles, but just re-create them after open. With careful planning, this is a safe and clever way to conserve resources during backup. It might actually *reduce* MTR for the recovery. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton 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: Hot backup and TEMP tablespace
Hum, I apologize. Did not read well enough. -Original Message- Thomas F Sent: Monday, January 28, 2002 9:16 AM To: Multiple recipients of list ORACLE-L Kimberly, That is an excellent point! But the original writer was taking a hot backup of the TEMP tablespace, so she must be using an older version of Oracle. Maybe she should migrate to 8i? :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, January 28, 2002 10:16 AM To: Multiple recipients of list ORACLE-L If you are using a temp tablespace with a temp file you really are not suppose to back it up. This comes from Oracle, not folks trying to save time. You cannot put them into backup mode so how do you recommend backing them up? -Original Message- Thomas F Sent: Monday, January 28, 2002 5:31 AM To: Multiple recipients of list ORACLE-L Traci, Yes, you can skip backing up the TEMP tablespaces, and as Kirti suggested, and have instructions in your recovery manual on how to rebuild the tablespace when you are going thru a recovery. In my humble opinion, I would not do this. It's kinda like using duct tape to cover a crack in the window - it works, but its really not right. Do you really want to be in the position of performing extra recovery steps just to save yourself some time during backups? Why stop at backing up the TEMP tablespace - why not the ROLLBACK tablespace - this could be dropped and re-created also. Why not INDEX tablespaces - heck, if you have the scripts, these could be re-created too! My point (as a professional DBA) is that, backups should be intact so that you can recover easily without having to do extra work. It is really one less thing to remember and have to worry about. If your real issue is that you cannot perform your backup in the time allotted, or that you need more disk space to perform your backup, you should either investigate Rman (as Jared suggested), or buy more disk - both should be considered the cost of doing business. I am a strong proponent of Rman - it solves MANY backup and recovery problems. Hope this helps! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, January 26, 2002 10:40 AM To: Multiple recipients of list ORACLE-L I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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
Re: Hot backup and TEMP tablespace
TEMPFILEs and TEMPORARY tablespace Saving time during backup is nice. But when your database goes down, all of a sudden the time it takes to get it back up becomes real important. Everybody is calling you to ask, How long until the database is back up? Using true temporary tablespaces and tempfiles means you can skip backing up the temp tablespace, and when you need to recover it you can recreate the temp tablespace fast. Otherwise, recreating it can take a long time, meaning the time to recover a crashed database may take longer. Check Jonathan Lewis's book (Practical Oracle 8i) -- he covers this well in his chapter on temp tablespaces. You'll may also want a simple script available to create the temp tablespace, so you won't have to write it during a recovery, and you'll probably want to practice on a test database. It would be nice to document this too in a DBA notebook -- if you leave, a new DBA who has to recover the database may be puzzled to find the backup is incomplete, they may not know how to create a temp tablespace quickly, etc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore 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).
Hot backup and TEMP tablespace
I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Hot backup and TEMP tablespace
You are right. Excluding TEMP tablespace from hot backups is not a problem at all. Your in-house instructions/docs about recovering the database should spell out what to do after performaing the recovery. Also if your version of Oracle supports the TEMPFILEs and TEMPORARY tablespaces, consider those for TEMP tablespaces. Please refer to Note# 160426.1 on Metalink for details. - Kirti -Original Message- Sent: Saturday, January 26, 2002 9:40 AM To: Multiple recipients of list ORACLE-L I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Hot backup and TEMP tablespace
Using RMAN will reduce your backup time, as it only backs up used blocks. It also takes less space. Jared On Saturday 26 January 2002 07:40, [EMAIL PROTECTED] wrote: I was wondering if anyone could offer any suggestions to this issue, or the pro's and con's... We are trying to shorten the time frame that it takes to complete our Oracle hot backups, and the subsequent file copies to tape. I have read that it is ok to skip the TEMP tablespace, and then do an off-line drop of the datafile(s) in the temporary tablespace, drop the tablespace, and then recreate it. Our temporary tablespaces are 900mb, and they take a chunk of the total time it takes to complete the hot backups. Are there any issues or ramifications of not including that tablespace in the event of a recovery? It seems to me that it would be much quicker to re-create that tablespace if needed. Any suggestions are greatly appreciated. Traci Rebman Oracle Database Administrator R.R. Donnelley Sons - Financial Division Lancaster, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
Convert TEMP tablespace to LMT?
What is the best way to convert a dictionary-managed TEMP tablespace to locally managed? Heres my situation, I created an 8.1.7 database with all dictionary-managed tablespaces. Then, I converted all tablespaces to locally managed, except SYSTEM and TEMP. The conversion packaged refused to convert TEMP to LMT so it seems that I must drop the TEMP tablespace and re-create it as LMT. My question is, do I need to create an intermediate, like TEMP2, and point everything to it while Im dropping and re-creating TEMP? Thanks for your advice, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you.
Re: Convert TEMP tablespace to LMT?
Should be created as temporary locally managed issuing the stmt: create temporary tablespace temp2 tempfile .. extent management local.. After that, drop the other one. Regards. --- David Wagoner [EMAIL PROTECTED] wrote: What is the best way to convert a dictionary-managed TEMP tablespace to locally managed? Here's my situation, I created an 8.1.7 database with all dictionary-managed tablespaces. Then, I converted all tablespaces to locally managed, except SYSTEM and TEMP. The conversion packaged refused to convert TEMP to LMT so it seems that I must drop the TEMP tablespace and re-create it as LMT. My question is, do I need to create an intermediate, like TEMP2, and point everything to it while I'm dropping and re-creating TEMP? Thanks for your advice, david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110 Durham, NC 27703 Tel. (919) 941-4645 Fax (919) 474-0735 Email mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web http://www.arsenaldigital.com/ http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you. = ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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).
TEMP Tablespace Problem
Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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 Tablespace Problem
Hi Assuming that you actually use the TEMP tablespace as a real temp tablespace and didn't put any actual data in it, you can just go ahead recreate it by reusing the old datafile. Ken Janusz schrieb: Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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 Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke 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 Tablespace Problem
Use the REUSE option on the CREATE TABLESPACE command. Ken Janusz ken.janusz To: Multiple recipients of list ORACLE-L @sufsys.com [EMAIL PROTECTED] Sent by: rootcc: Subject: TEMP Tablespace Problem 12/11/2001 10:50 AM Please respond to ORACLE-L Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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 Tablespace Problem
Ken - I assume you dropped the TEMP tablespace, which doesn't delete the datafile. I'm assuming you are on Unix. Just remove the data file and recreate the TEMP tablespace the same way as you originally did. I believe that the temp tablespace settings for all users will be unaffected, but you might check one or two to make sure. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 11, 2001 9:50 AM To: Multiple recipients of list ORACLE-L Somehow I deleted the TEMP tablespace but not the datafile. How do I go about recreating this tablespace? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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).
rollback for temp tablespace
Hi lists, Can anybody clarify this: Do the sort/idx rebuild etc.,(tasks that use temp tablespace), use rollback segments? I need to rebuild indexes of 20 Gig to a new tablespace. Do I need to verify the RBS free space for this.? Thnx and Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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: rollback for temp tablespace
They do use some rollback but not the volumes you'd typically need to worry about. The rollback space is for mods to the data dictionary as your segments are created. hth connor --- Tatireddy, Shrinivas (MED, Keane) [EMAIL PROTECTED] wrote: Hi lists, Can anybody clarify this: Do the sort/idx rebuild etc.,(tasks that use temp tablespace), use rollback segments? I need to rebuild indexes of 20 Gig to a new tablespace. Do I need to verify the RBS free space for this.? Thnx and Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Nokia 5510 looks weird sounds great. Go to http://uk.promotions.yahoo.com/nokia/ discover and win it! The competition ends 16 th of December 2001. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: rollback for temp tablespace
By the way Does this process (rebuilding of indexes) generate redo? Srinivas -Original Message- Sent: Wednesday, December 05, 2001 6:30 AM To: Multiple recipients of list ORACLE-L They do use some rollback but not the volumes you'd typically need to worry about. The rollback space is for mods to the data dictionary as your segments are created. hth connor --- Tatireddy, Shrinivas (MED, Keane) [EMAIL PROTECTED] wrote: Hi lists, Can anybody clarify this: Do the sort/idx rebuild etc.,(tasks that use temp tablespace), use rollback segments? I need to rebuild indexes of 20 Gig to a new tablespace. Do I need to verify the RBS free space for this.? Thnx and Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Nokia 5510 looks weird sounds great. Go to http://uk.promotions.yahoo.com/nokia/ discover and win it! The competition ends 16 th of December 2001. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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: rollback for temp tablespace
Definitely. Unless you say NOLOGGING or UNRECOVERABLE (depending on the versions). But data dictionary changes are always logged irrespective of logging mode. Best Regards, K Gopalakrishnan -Original Message- Shrinivas (MED, Keane) Sent: Wednesday, December 05, 2001 2:30 AM To: Multiple recipients of list ORACLE-L By the way Does this process (rebuilding of indexes) generate redo? Srinivas -Original Message- Sent: Wednesday, December 05, 2001 6:30 AM To: Multiple recipients of list ORACLE-L They do use some rollback but not the volumes you'd typically need to worry about. The rollback space is for mods to the data dictionary as your segments are created. hth connor --- Tatireddy, Shrinivas (MED, Keane) [EMAIL PROTECTED] wrote: Hi lists, Can anybody clarify this: Do the sort/idx rebuild etc.,(tasks that use temp tablespace), use rollback segments? I need to rebuild indexes of 20 Gig to a new tablespace. Do I need to verify the RBS free space for this.? Thnx and Regards, Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Nokia 5510 looks weird sounds great. Go to http://uk.promotions.yahoo.com/nokia/ discover and win it! The competition ends 16 th of December 2001. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) 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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Question - Oracle's TEMP tablespace and a user's temporary
Well... Again - sort of. Temporary segments for normal stuff (e.g. SQL order by, joins, etc.) will occur in USER_TEMP. Sorts for segment operations, parallel index builds and the like, may occur elsewhere. -Don Granaman (OraSaurus - Honk if you remember OPS ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 17, 2001 1:18 PM temporary Don; Thanks very much for your reply. All very understandable. However, let me ask a direct question; does this mean that, given a user having been explicitly assigned a temporary tablespace (ie. USER_TEMP), that all of that user's sorts and temporary segments will occur in the assigned (USER_TEMP) tablespace and nowhere else. Thanks again. Michael L. Petrus GE Auto Warranty Services 7125 W. Jefferson Av. #200 Lakewood, CO 80235 Database Administrator Phone: (303) 987 4129 Fax: (303) 987 4298 Email: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 17, 2001 1:40 AM To: Multiple recipients of list ORACLE-L temporary ta Not exactly... TEMP is an arbitrary (but logical, recommended, and OFA compliant) name for a tablespace. It could just as well be named GEORGE for all that $ORACLE_HOME/bin/oracle or any of its kin care. TEMPORARY_TABLESPACE is an attribute of a user. (i.e. select TEMPORARY_TABLESPACE from ALL_USERS; or from SYS.DBA_USERS). All users (including SYS and SYSTEM) are either implicitly or explicitly assigned a temporary tablespace. Unfortunately, it defaults to the worst possible choice -SYSTEM! Typically, users are explicitly assigned TEMP as their temporary tablespace. [Oracle (AKA: sys) doesn't usually do much that requires a lot of temporary space.] The vast majority of Oracle databases have only one such tablespace, but (logically) partitioning users among multiple such tablespaces (TEMP01, TEMP02, ...) is sometimes advisable. Making TEMP (and its ilk) truly TEMPORARY (as opposed to PERMANENT) is always advisable. -Don Granaman (OraSaurus - Honk if you remember OPS ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 16, 2001 5:15 PM temporary ta Environment: Oracle v8.1.7 Sun / Solaris == == Can someone explain what activities occur in Oracle's TEMP tablespace as opposed to what occurs in a user's assigned TEMPORARY tablespace? It is my assumption that Oracle's TEMP tablespace is the area used by Oracle for it's processes to sort in. It is also my assumption that the user specific TEMPORARY tablespace is used to hold temporary segments that are created when the user builds an index or creates a table, etc.. If I am off base please set me straight. Thanks in advance. Michael L. Petrus GE Auto Warranty Services 7125 W. Jefferson Av. #200 Lakewood, CO 80235 Database Administrator Phone: (303) 987 4129 Fax: (303) 987 4298 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Petrus, Mike (CAP, GEFA) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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
TEMP Tablespace
TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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 Tablespace
Wouldn't it need to have something in it? -Original Message- Sent: 25 September 2001 16:10 To: Multiple recipients of list ORACLE-L TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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 Tablespace
How was your TEMP tablespace created? Was it created using a temp datafile? Not all information that is normally available with tablespaces are valid when using a temporary tablespace with a temporary datafile. -Original Message- Sent: Tuesday, September 25, 2001 8:10 AM To: Multiple recipients of list ORACLE-L TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith 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 Tablespace
What tablespace are the temp segments showing up in? It may not be in TEMP. HTH, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 11:10 AM TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini 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 Tablespace
Title: RE: TEMP Tablespace Hi Connie, Because there isn't anything stored in there (a segment) permanently. But when I look at my db (well, my horsing-around-and-breaking-stuff db) I see this. My temp tablespace is not permanent. 1 select segment_name from dba_segments 2* where segment_Type = 'TEMPORARY' (VIKING-SYSTEM)/ SEGMENT_NAME -- 4.13234 What on earth is that? Seems useless to me. If I want to know what's going on in temp, I look at dba_tablespaces and dba_free_space. Correct me if I'm wrong... Why did you clear out your temp? Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Connie Milliken [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, September 25, 2001 11:10 AM To: Multiple recipients of list ORACLE-L Subject: TEMP Tablespace TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Connie Milliken 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 Tablespace
Title: RE: TEMP Tablespace -Original Message- From: Connie Milliken [mailto:[EMAIL PROTECTED]] TEMP tablespace is not listed when I query dba_segments. However, it does show up in OEM and through OEM it does appear to be online. Yesterday, I briefly took the TEMP tablespace offline and then back online again to make sure it was completely cleared out (did not appear to be any active sessions in the database at the time). Have also bounced the database. Any ideas on why it still would not be showing up when querying dba_segments? Is this a temporary tablespace that's locally managed with a temporary file? i.e. are the datafiles for that tablespace shown in dba_temp_files instead of dba_data_files? In that case, I don't think you'll see anything in the dba_segments view, but you would have to look at views like v$temp_space_header and v$temp_extent_map.
Disk config. for datafiles of Temp Tablespace
Hi All, We have data warehouse which uses arround 34 GB of temporary tablespace. The datafiles for this are tempfiles and the tablespace is LMT. We use EMC storage. The tempfiles are on two file systems which inturn are mirrored and striped across three 8 GB disks. We are seeing heavy IO on these files and Our direct path write waits are high. Could you guys suggest a better file layout of disk configuration? __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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: Disk config. for datafiles of Temp Tablespace
What about mount point on same controller? Split them on different controller if not ? What is the maximum usage of your TEMP tablespace ? and size of datafiles...? Try to reduce size of your temp tablespace as it looks very large Better solution is Datfiles of 501MB each split on different mount points configured with different contollers...Please also check for such querries resulting in Cartesian Joins resulting lot of IO's and temp usage... Regards MOHAMMAD RAFIQ Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Aug 2001 07:56:46 -0800 Hi All, We have data warehouse which uses arround 34 GB of temporary tablespace. The datafiles for this are tempfiles and the tablespace is LMT. We use EMC storage. The tempfiles are on two file systems which inturn are mirrored and striped across three 8 GB disks. We are seeing heavy IO on these files and Our direct path write waits are high. Could you guys suggest a better file layout of disk configuration? __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil 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). _ 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).
RE: Temp tablespace issue
Y'day I got this errors, I tried to resize the file, But I could't succeed! Later I did wakeup SMON and tried with alter tablespace temp ..(pctincrease 0)..But still I getting ora-600 with ora-01114/ora-07376 errors..Then I put that particular temp file in offline!!..I problems increased!! Then I try to put online..It asked recovery .I applied the recovery..Problem is gone!! My space is okay. But I find for simple temp space insuffcient, just wake up SMON!! Its works well. But any ora-600 problems..STILL PUZZLE?? Mon Jul 23 08:26:13 2001 Errors in file /software/app/oracle/admin/baanIV/udump/ora_65640.trc: ORA-00600: internal error code, arguments: [3509], [2], [], [], [], [], [], [] ORA-01114: IO error writing block to file 166 (block # 134552) ORA-07376: sfwfb: write error, unable to write database block. IBM AIX RISC System/6000 Error: 27: File too large Additional information: 134552 Thanks Raghu. From: Deshpande, Kirti [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: RE: Temp tablespace issue Date: Tue, 24 Jul 2001 08:52:53 -0500 If your are getting ORA-600 error for seemingly simple operations, I think it is time to get Oracle Support involved as soon as possible. Good Luck. Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Pankaj Behl [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 8:37 AM To: LazyDBA.com Discussion Subject:Temp tablespace issue Hi Guru's Today seems to be the day for awakenings! Our temp tablespace is about 10GB and the application uses up to about 3.5 GB. We had a space issue on the development box and i tried to reclaim some space by resizing the temp tablespace. But..when i tried to resize the TEMP tablespace i had ORA-600 errors reported in the log files and i had to kill the sql session that was performing the resize. I have not noticed any problems since. I also tried to drop the temp tablespace with similar problems. What all am i missing? Would appreciate any help in this regard as we are still up and running and still need to tackle the space issue. Thanks and Regards, Pankaj Behl __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ 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: Raghu Kota 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).
temp tablespace is permanent tablespace
Hi, One of the generic users (multiple people use the same logon/password) on one of my db's has been assigned temporary tablespace which is a permanent tablespace type, i.e. (select contents from dba_tablespaces where tablespace_name=my_ts_name; returns PERMANENT). I was told by let it stay that way. I am investigating the whole thing (just for myself). Is there any true reason why the user would need their temp tablespace to be permanent? Could my situation cause any major problems? If so, what things I need keep my eye on in order to avoid them? Thank you. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
