Re: Space management failures on autoextend datafiles
Hi Igor, Glad you enjoyed Jonathan's talks! Next time, you'll have to join us at his seminar which followed AAOUG and wrapped up yesterday! I'm actually heading over to the hotel to pick him up and take hime to the airport in a few minutes. It was a really great three days. I think all 24 people we had really enjoyed themselves! -Mark PS I should have introduced myself. I walked right by you at AAOUG, and I saw the name tag Igor Neyman, and I couldn't think why I knew the name! Now I know;-) On Thu, 2002-11-07 at 11:14, Igor Neyman wrote: I've heard about the same problem yesterday (while attending Ann Arbor OUG conference), unfortunately - no solution, I think, they opened TAR. BTW, it was very good conference, great presentations by Jonathan Lewis. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 10:39 AM Subject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak 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: Space management failures on autoextend datafiles
Let's think about this for a minute. You create and index called idx1 using a designated tablespace that has sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index. The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does not know that the index will complete, be aborted, crash, etc so it can not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each temporary function then the database could artificially expand when the functions were only temporary in nature and the compounded effect could cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted. Just a few thoughts and ideas. Ron [EMAIL PROTECTED] 11/07/02 04:43PM maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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
RE: Space management failures on autoextend datafiles
Ron, I don't believe all of that to be true. In your example idx2 should be created and the tablespace should extend. Otherwise what would be the point of having AUTOEXTEND on any tablespace which held only indexes? I agree that Oracle will use a temporary segment to hold the index data until it is fully created but Oracle will extend the datafile regardless. In fact, when I tested this at 8.1.7.3 and 9.2 even an index rebuild extended the datfile in order to accomodate the new index. regards, Mike Hately -Original Message- Sent: Friday, November 08, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Let's think about this for a minute. You create and index called idx1 using a designated tablespace that has sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index. The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does not know that the index will complete, be aborted, crash, etc so it can not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each temporary function then the database could artificially expand when the functions were only temporary in nature and the compounded effect could cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted. Just a few thoughts and ideas. Ron [EMAIL PROTECTED] 11/07/02 04:43PM maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: Space management failures on autoextend datafiles
as i said. maybe temp segments don't cause autoextend :) --- Ron Rogers [EMAIL PROTECTED] wrote: Let's think about this for a minute. You create and index called idx1 using a designated tablespace that has sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index. The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does not know that the index will complete, be aborted, crash, etc so it can not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each temporary function then the database could artificially expand when the functions were only temporary in nature and the compounded effect could cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted. Just a few thoughts and ideas. Ron [EMAIL PROTECTED] 11/07/02 04:43PM maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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
RE: Space management failures on autoextend datafiles
I just tried a cartesian product/sort and the TEMP tablespace, which is set up as temporary and using a tempfile was able to autoextend. Of course, the next step is to set up a TEMP tablespace as temporary using a datafile, then permanent and see what happens...volunteers? In regards to the issue of not autoextending in the event of a statement failure. If this were the case, then almost no autoextension would occur. If I insert 10 million rows into a table, causing the datafile to autoextend, I could issue a rollback and not 'save' the rows. However, the table would still have allocated the additional space (and the HWM would be WAY out there...). Dan Fink -Original Message- Sent: Thursday, November 07, 2002 2:44 PM To: Multiple recipients of list ORACLE-L maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: Fink, Dan 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
RE: Space management failures on autoextend datafiles
Mike, In the previous discussion on this thread the problem was that the autoextent would not work for a temp segment created during the index creation and the problem was solved by extending the tablespace to handle the temp segments. Ron [EMAIL PROTECTED] 11/08/02 09:18AM Ron, I don't believe all of that to be true. In your example idx2 should be created and the tablespace should extend. Otherwise what would be the point of having AUTOEXTEND on any tablespace which held only indexes? I agree that Oracle will use a temporary segment to hold the index data until it is fully created but Oracle will extend the datafile regardless. In fact, when I tested this at 8.1.7.3 and 9.2 even an index rebuild extended the datfile in order to accomodate the new index. regards, Mike Hately -Original Message- Sent: Friday, November 08, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Let's think about this for a minute. You create and index called idx1 using a designated tablespace that has sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index. The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does not know that the index will complete, be aborted, crash, etc so it can not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each temporary function then the database could artificially expand when the functions were only temporary in nature and the compounded effect could cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted. Just a few thoughts and ideas. Ron [EMAIL PROTECTED] 11/07/02 04:43PM maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you
RE: Space management failures on autoextend datafiles
Hi Ron, you're quite right and I'm pointing out that this functionality (auto-extension of a file to accomodate a tempoary segment) works fine for me. That suggests that (bugs aside) temporary segments are quite capable of forcing a datafile to extend. The thrust of your earlier mail seemed to be that this was a deliberate design choice on the part of Oracle rather than a bug. Apologies if I misundestood your intention. Regards, Mike -Original Message- Sent: Friday, November 08, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Mike, In the previous discussion on this thread the problem was that the autoextent would not work for a temp segment created during the index creation and the problem was solved by extending the tablespace to handle the temp segments. Ron [EMAIL PROTECTED] 11/08/02 09:18AM Ron, I don't believe all of that to be true. In your example idx2 should be created and the tablespace should extend. Otherwise what would be the point of having AUTOEXTEND on any tablespace which held only indexes? I agree that Oracle will use a temporary segment to hold the index data until it is fully created but Oracle will extend the datafile regardless. In fact, when I tested this at 8.1.7.3 and 9.2 even an index rebuild extended the datfile in order to accomodate the new index. regards, Mike Hately -Original Message- Sent: Friday, November 08, 2002 12:39 PM To: Multiple recipients of list ORACLE-L Let's think about this for a minute. You create and index called idx1 using a designated tablespace that has sufficient space to hold the complete index. You create a new index idx2 on the table using the same tablespace and you think that it should autoextend to hold the permanent index. The system generates the index and starts placing the temporary index named something like 123.123 in the tablespace. This index is a temporary index until the system has completed creating the entire index. Then it will make the index name permanent as idx2 and use the space accordingly with the required extents and autoextend. Oracle does not know that the index will complete, be aborted, crash, etc so it can not make any permanent assignment to the extents( that is why it is called temporary) . Oracle would permanently extent tablespaces for each temporary function then the database could artificially expand when the functions were only temporary in nature and the compounded effect could cause a ripple effect. The backup size would expand, search functions could take longer because of the increased size, disk space would be wasted. Just a few thoughts and ideas. Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: Space management failures on autoextend datafiles
Dan, is your datafile approaching (or past) the maxsize setting? AUTOEXTEND functionality won't grow a file beyond this but I believe it's possible to manuallly resize the file beyond the MAXSIZE figure. That would explain the apparent anomaly. regards, Mike Hately -Original Message- Sent: Thursday, November 07, 2002 3:39 PM To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately, Mike (NESL-IT) 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: Space management failures on autoextend datafiles
never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Re: Space management failures on autoextend datafiles
I've heard about the same problem yesterday (while attending Ann Arbor OUG conference), unfortunately - no solution, I think, they opened TAR. BTW, it was very good conference, great presentations by Jonathan Lewis. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 10:39 AM Subject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Re: Space management failures on autoextend datafiles
Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: Space management failures on autoextend datafiles
Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
Re: Space management failures on autoextend datafiles
Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: Space management failures on autoextend datafiles
I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: Space management failures on autoextend datafiles
Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 1:09 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Space management failures on autoextend datafiles I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 10:34 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Subject: RE: Space management failures on autoextend datafiles Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message-From: Yechiel Adar [mailto:[EMAIL PROTECTED]]Sent: Thursday, November 07, 2002 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Space management failures on autoextend datafiles Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel AdarMehish - Original Message - From: Paulo Gomes To: Multiple recipients of list ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM Subject: RE: Space management failures on autoextend datafiles never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: quinta-feira, 7 de Novembro de 2002 15:39To: Multiple recipients of list ORACLE-LSubject: Space management failures on autoextend datafiles I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink
RE: Space management failures on autoextend datafiles
maybe temp segments don't cause an autoextend? at least it's consistent --- Fink, Dan [EMAIL PROTECTED] wrote: Update... I tried the same test with 8.1.7 on Solaris 8. Same result... -Original Message- Sent: Thursday, November 07, 2002 1:09 PM To: Multiple recipients of list ORACLE-L I just manually expanded the file and the index build finished with no problem. I also set the USER_DATA ts as autoextend and it extended several times without error. The only difference is in the fact that the INDEX segment is initially created as a TEMP segment. Interesting -Original Message- Sent: Thursday, November 07, 2002 10:34 AM To: Multiple recipients of list ORACLE-L Dan, That's exactly, what someone described yesterday, it was LMT with autoallocate, and they made sure, that there was plenty of space on the hard drive for TEMP file to grow. The only difference: it was not Solaris - it was AIX. Igor Neyman, OCP DBA [EMAIL PROTECTED] mailto:ineyman;perceptron.com - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 12:15 PM Igor and Yechiel, Thanks for the responses. I'm glad to hear that I am not the only one experiencing the problem. I should have also mentioned that the tablespace is LMT with autoallocate and is nowhere near the max size. Dan Fink -Original Message- Sent: Thursday, November 07, 2002 9:35 AM To: Multiple recipients of list ORACLE-L Hi Paulo When creating an index, or CTAS, oracle use temp segments while building and rename them after the build finish. So if you do not have enough space you will get: unable to allocate TEMP segment. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list mailto:ORACLE-L;fatcity.com ORACLE-L Sent: Thursday, November 07, 2002 6:04 PM never heard on this problem but are u sure table the temp tablespace of the user executing the commeand is temp and not user_indx??? regards Paulo -Original Message- Sent: quinta-feira, 7 de Novembro de 2002 15:39 To: Multiple recipients of list ORACLE-L I have a 9.2 database running on Solaris 8. I'm creating some test tables with indexes. The USER_INDX tablespace's datafile is set to autoextend (as are TEMP and USER_DATA). When the system attempts to create indexes, instead of auotextending the datafile (there is plenty of space on the device), it throws an ORA-01652: unable to extend temp segment by 128 in tablespace USER_INDX error. If I manually resize the datafile and rerun it, no problems. Anyone else heard of this behavior? I can't find anything on Metalink that fits the problem definition. Dan Fink __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
