RE: ORA-1653: unable to extend table - Why?
Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Miller, Jay 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: ORA-1653: unable to extend table - Why?
How is the insert being used? Is it one row per insert? Is the column values hardcoded or passed in variables? What is the maximum row length? -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Miller, Jay 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
RE: ORA-1653: unable to extend table - Why?
This is what Oracle came up with just now: Hi Jay, Based on my research: 1/From note: 1029850.6 Freelist and Freelist Groups: It is advised that the space calculated as (100% - (PCTFREE + PCTUSED)) be greater than the maximum size of a table row. Based on the above formula, the leftover space would be @614 bytes. Since your largest row is bigger than the size of your datablock, you can try adjusting your pctused to at least accomodate your average row. Using the above calculation it would be 35%. 2/ In bug 450349 TABLES ARE CONTINUALLY TAKING NEW EXTENTS WITH NUMEROUS BLOCKS ON FREELIST, I found the following statement in an unpublished section: The pctused = 80 may be part of the issue. A block will be put on the freelist if its used space falls below 80%. However, the data layer will consider at most 5 blocks on the freelist, and if none are acceptable it will request a new block. If the size of the row being inserted is greater than 20%, then the block won't be used. They should try running with a smaller pctused value (65 or 40) While the bug was logged against Oracle 7, the design has not changed that much. ACTION PLAN: === - modify the storage parameter for the table, reduce the pctfree/pctused as appropriate -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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:
RE: ORA-1653: unable to extend table - Why?
What Oracle said is not different than what I sent before (see below). The problem is that the statistics of the table indicates that the average free space in the free blocks is almost 100% and this contradicts with the idea below. At least there will be room for one row if the space in the block is smaller than the average. Anyway, simply the behavior you see contradicts with the documented behavior for the free lists. So let them give you the facts (I doubt!). regards, Waleed -Original Message- Sent: Wednesday, December 04, 2002 5:49 PM To: Multiple recipients of list ORACLE-L I guess this is reason. pct_used and pct_free are very closed. after the deletion if the pct_free is just below 75% it will join the free list. Suppose the block header is Y bytes. And you have pct_free = 10% and pct_used = 75%, this means if a block is 30% free (70% used) it will have (4096 - Y ) * (90 - 70) = less than 800 bytes for insertion before reaching pct_free. And you have the average row length = 1895, so most of the allocations will fail. The problem is very small block size in comparison to row size and high pct_used. For now lower your pct_used. HTH Waleed -Original Message- Sent: Wednesday, December 04, 2002 3:39 PM To: Multiple recipients of list ORACLE-L 1895 -Original Message- Sent: Wednesday, December 04, 2002 2:34 PM To: Multiple recipients of list ORACLE-L What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting
RE: ORA-1653: unable to extend table - Why?
One row/insert. One commit every 1-6 inserts (rows). Column values are passed to pl/sql procedure which does the insert (i.e,. passed in variables). Maximum row length: I assume you mean the largest row in the table? Does anyone have an easy way to get this? Other than applying formulas to each individual column based on datatype and length of the value? Jay -Original Message- Sent: Friday, December 06, 2002 11:51 AM To: Multiple recipients of list ORACLE-L How is the insert being used? Is it one row per insert? Is the column values hardcoded or passed in variables? What is the maximum row length? -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: Miller, Jay 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
RE: ORA-1653: unable to extend table - Why?
Okay, now that I've read this over it makes sense (and thanks again to Kirti who supplied the same note albeit without the quote from the unpublished section on bug 450349. Apparently Oracle will check a maximum of 5 blocks on freelist for sufficient free space for an insert before grabbing a new extent. So it looks like I want to decrease PCTUSED, probably to about 40%. I can also safely reduce PCTFREE since there won't be more than 2 rows in the block and at most 2 bytes are be updated per row. But will this solve my problem in the near term? My understanding is that simply changing the PCT USED won't move the problematic blocks off the freelist until some sort of DML touches the block. Am I correct in this and if so is there any way to resolve it? Jay -Original Message- Sent: Friday, December 06, 2002 12:25 PM To: Multiple recipients of list ORACLE-L This is what Oracle came up with just now: Hi Jay, Based on my research: 1/From note: 1029850.6 Freelist and Freelist Groups: It is advised that the space calculated as (100% - (PCTFREE + PCTUSED)) be greater than the maximum size of a table row. Based on the above formula, the leftover space would be @614 bytes. Since your largest row is bigger than the size of your datablock, you can try adjusting your pctused to at least accomodate your average row. Using the above calculation it would be 35%. 2/ In bug 450349 TABLES ARE CONTINUALLY TAKING NEW EXTENTS WITH NUMEROUS BLOCKS ON FREELIST, I found the following statement in an unpublished section: The pctused = 80 may be part of the issue. A block will be put on the freelist if its used space falls below 80%. However, the data layer will consider at most 5 blocks on the freelist, and if none are acceptable it will request a new block. If the size of the row being inserted is greater than 20%, then the block won't be used. They should try running with a smaller pctused value (65 or 40) While the bug was logged against Oracle 7, the design has not changed that much. ACTION PLAN: === - modify the storage parameter for the table, reduce the pctfree/pctused as appropriate -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used
RE: ORA-1653: unable to extend table - Why?
They had one new piece of information. That is the unpublished note on Bug#450349 which states that if Oracle doesn't have enough space to insert in the first 5 blocks it tries then it will request a new block. So even if most of the blocks are available if there happen to be 5 in a row near the top of the freelist that don't have enough room for the row then there will be a problem. Now the unlikely part of this is that Bug 450349 was apparently logged against version 7. It seems odd that it would not be fixed in 8.1.7. The rep's statement that the design has not changed that much seems odd. Jay -Original Message- Sent: Friday, December 06, 2002 1:45 PM To: Multiple recipients of list ORACLE-L What Oracle said is not different than what I sent before (see below). The problem is that the statistics of the table indicates that the average free space in the free blocks is almost 100% and this contradicts with the idea below. At least there will be room for one row if the space in the block is smaller than the average. Anyway, simply the behavior you see contradicts with the documented behavior for the free lists. So let them give you the facts (I doubt!). regards, Waleed -Original Message- Sent: Wednesday, December 04, 2002 5:49 PM To: Multiple recipients of list ORACLE-L I guess this is reason. pct_used and pct_free are very closed. after the deletion if the pct_free is just below 75% it will join the free list. Suppose the block header is Y bytes. And you have pct_free = 10% and pct_used = 75%, this means if a block is 30% free (70% used) it will have (4096 - Y ) * (90 - 70) = less than 800 bytes for insertion before reaching pct_free. And you have the average row length = 1895, so most of the allocations will fail. The problem is very small block size in comparison to row size and high pct_used. For now lower your pct_used. HTH Waleed -Original Message- Sent: Wednesday, December 04, 2002 3:39 PM To: Multiple recipients of list ORACLE-L 1895 -Original Message- Sent: Wednesday, December 04, 2002 2:34 PM To: Multiple recipients of list ORACLE-L What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event
RE: ORA-1653: unable to extend table - Why?
I remember reading that when a free block fails the space requirements for an insert it gets flagged 'unlink' and either it gets unlinked or it may require to fail five times before it gets unlinked. Waleed -Original Message- Sent: Friday, December 06, 2002 2:34 PM To: Multiple recipients of list ORACLE-L Okay, now that I've read this over it makes sense (and thanks again to Kirti who supplied the same note albeit without the quote from the unpublished section on bug 450349. Apparently Oracle will check a maximum of 5 blocks on freelist for sufficient free space for an insert before grabbing a new extent. So it looks like I want to decrease PCTUSED, probably to about 40%. I can also safely reduce PCTFREE since there won't be more than 2 rows in the block and at most 2 bytes are be updated per row. But will this solve my problem in the near term? My understanding is that simply changing the PCT USED won't move the problematic blocks off the freelist until some sort of DML touches the block. Am I correct in this and if so is there any way to resolve it? Jay -Original Message- Sent: Friday, December 06, 2002 12:25 PM To: Multiple recipients of list ORACLE-L This is what Oracle came up with just now: Hi Jay, Based on my research: 1/From note: 1029850.6 Freelist and Freelist Groups: It is advised that the space calculated as (100% - (PCTFREE + PCTUSED)) be greater than the maximum size of a table row. Based on the above formula, the leftover space would be @614 bytes. Since your largest row is bigger than the size of your datablock, you can try adjusting your pctused to at least accomodate your average row. Using the above calculation it would be 35%. 2/ In bug 450349 TABLES ARE CONTINUALLY TAKING NEW EXTENTS WITH NUMEROUS BLOCKS ON FREELIST, I found the following statement in an unpublished section: The pctused = 80 may be part of the issue. A block will be put on the freelist if its used space falls below 80%. However, the data layer will consider at most 5 blocks on the freelist, and if none are acceptable it will request a new block. If the size of the row being inserted is greater than 20%, then the block won't be used. They should try running with a smaller pctused value (65 or 40) While the bug was logged against Oracle 7, the design has not changed that much. ACTION PLAN: === - modify the storage parameter for the table, reduce the pctfree/pctused as appropriate -Original Message- Sent: Friday, December 06, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Very interesting! I was out sick yesterday so I'm just getting caught up on the email today. There are currently 898334 rows and 2654300 blocks in the table (the number of rows will grow over the next 2 months before the next big delete which is done quarterly). But this does seem to imply that it is only allocating one row/block. Might adding more freelists enable it to make more use of the available blocks by avoiding timeouts while walking the freelist? I've also been investigating the application and have come across some annoying features that unfortunately the developer assures me can't change for various reasons. One is that the loading process is doing frequent commits (it commits on the account level which will be usually be one insert but unlikely to be more than 6). Also it is doing a Select from Dual for every insert. Don't know if this is relevant to anything other than performance though. Matt: You suggest changing the storage parameters or by changing the block size. Changing the block size isn't really an option just now (though once I upgrade to 9i I'll seriously consider changing it for just this tablespace). What storage parameter changes did you have in mind? Waleed: The table is not partitioned. Extent size is 25Meg. Jay Miller x48355 -Original Message- Sent: Thursday, December 05, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll
RE: ORA-1653: unable to extend table - Why?
How badly do you want the space back? I believe you will indeed need to touch each row. You could update each row with something like (update set column-1=column-1) Good luck! Barb "Miller, Jay" [EMAIL PROTECTED] wrote: But will this solve my problem in the near term? My understanding is thatsimply changing the PCT USED won't move the problematic blocks off thefreelist until some sort of DML touches the block. Am I correct in this andif so is there any way to resolve it?JayDo you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: ORA-1653: unable to extend table - Why?
Or maybe Miller. It is, after all, the name of a well-known alcoholic beverage. Good thing my first name is not spelled Moans. I would probably not get many emails through spam filters. Mogens Miller, Jay wrote: Kirti, Thanks for suggesting the Note, I'm reading it now. I tried replying to you directly but my thank you was blocked by your company's spam filter. I'm really curious to know what key word flagged it as spam. Oracle? Jay -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even
Re: ORA-1653: unable to extend table - Why?
Well-known, perhapsnot particularly tasty, though HmmmThere isn't anyone on this list named Stella, is there? ;-) (As in Artois...) ;-) -Mark On Thu, 2002-12-05 at 03:39, Mogens Nørgaard wrote: Or maybe Miller. It is, after all, the name of a well-known alcoholic beverage. Good thing my first name is not spelled Moans. I would probably not get many emails through spam filters. Mogens Miller, Jay wrote: Kirti, Thanks for suggesting the Note, I'm reading it now. I tried replying to you directly but my thank you was blocked by your company's spam filter. I'm really curious to know what key word flagged it as spam. Oracle? Jay -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has
RE: ORA-1653: unable to extend table - Why?
Its not Miller or Moans or Oracle or OCP...:) Actually, it is the footerin the mail that tells you how to unsubscribe from the list. I receive the list mail on my business e-mail address. The spam detection mechanism considers such declaration a possible sign of spam. Our'Spam Police' were kind enough to let the direct list mail to mye-mail address come through (by checking a few other things in the message header), but if someone else forwards/ccs to me any of the list mail with that footer, it won't go through. - Kirti -Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002 2:39 AMTo: Multiple recipients of list ORACLE-LSubject: Re: ORA-1653: unable to extend table - Why?Or maybe Miller. It is, after all, the name of a well-known alcoholic beverage. Good thing my first name is not spelled Moans. I would probably not get many emails through spam filters.MogensMiller, Jay wrote: Kirti, Thanks for suggesting the Note, I'm reading it now. I tried replying to you directly but my thank you was blocked by your company's spam filter. I'm really curious to know what key word flagged it as spam. Oracle? Jay
RE: ORA-1653: unable to extend table - Why?
Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- 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 command for other information (like subscribing).
Re: ORA-1653: unable to extend table - Why?
Dan, I think you're right: There's exactly one row in each block and the rest of the space is wasted. No wonder the table is growing. So make it possible for Oracle to put more than one row into a block (or rather: to pack data more tightly into the blocks) either by changing the storage parameters or by changing the block size. I'm not sure fiddling with the _-parameter would help any. Mogens Fink, Dan wrote: Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: ORA-1653: unable to extend table - Why?
Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: ORA-1653: unable to extend table - Why?
I think you may have meant _walk_insert_threshold which by default is set to 0 (not set). Waleed Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: ORA-1653: unable to extend table - Why?
I came across the param in my Internals handbook (I'd never heard of it before). It could be different for the release/platform. The concept is probably the same. (?) -Original Message- Sent: Thursday, December 05, 2002 11:59 AM To: Multiple recipients of list ORACLE-L I think you may have meant _walk_insert_threshold which by default is set to 0 (not set). Waleed Dan, I think you nailed it! It will be interesting to see the # of blocks and # of rows in this table. - Kirti -Original Message- Sent: Thursday, December 05, 2002 9:44 AM To: Multiple recipients of list ORACLE-L Vitals: Average Row Length = 1895 Block Size = 4096 pct_free = 10% Threshold to put block off freelist = 3686 pct_used = 75% Threshold to put block on freelist = 3072 Average free space = 3895 Working with averages, there could be at most 2 rows per block. The Average free space is also very close to the block size, which indicates to me that the blocks on the free list are probably empty. Will a transaction insert a row into a block when it knows that the insert will push the block above the pct_free threshold? I can see logic on both sides. Don't insert because an update is more likely to cause row migration. Do insert because the space is wasted otherwise. After deleting 2 million rows, the # of blocks on the freelist is slightly over 2 million. Is this a coincidence? I'll take a guess and say that the insert processes are probably trying to acquire 1 block per 2 rows. Add in the other processes doing inserts, each one needs its own block if it is reusing it. I'm wondering if the insert transaction started walking the freelist, could not find an open block (because they were being used by other transactions) within a certain period (# of blocks checked or timeout) and decided to simply allocate another extent in order to enable the transaction to complete. In reviewing my notes/docs from the Internals Seminar (8i), there is a threshold (_release_insert_threshold) that will cause a new extent to be allocated even when there are blocks on the master free list. This seems a very likely scenario, given the large row size in comparison to the block size. Dan Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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). -- 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 command for other information (like subscribing).
RE: ORA-1653: unable to extend table - Why?
Hmm...not sure if this is what's biting you, but if you're out of ITL slots on the block, Oracle will move to the next block on the free list. If all the blocks on the free list have filled ITLs, Oracle will add an extent to the free list. I don't suppose you have MAXTRANS set to 1 or some other very low number? If so, and with concurrent inserts happening, it's possible that you could be allocating more blocks to the free list, even if there are tons of blocks already on the free list, simply due to the ITL shortage. (Note that this is why you'll never see mode 4 TX enqueue waits on insert to a table.) If MAXTRANS isn't really low, and if you're not doing direct-load (APPEND hint) inserts, then I'm stumped. -Mark On Tue, 2002-12-03 at 17:09, Fink, Dan wrote: Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can
Re: ORA-1653: unable to extend table - Why?
I always overlook things, so I'll just ask and hope it isn't already on the thread: Exactly what kind of activity is going on against this wonderful table? Is this table placed in an LMT? If yes, is it a Uniform LMT? Could you trace the processes/users doing stuff to the table so that we could see if there's contention for ITL slots or other such things? Mogens Mark J. Bobak wrote: Hmm...not sure if this is what's biting you, but if you're out of ITL slots on the block, Oracle will move to the next block on the free list. If all the blocks on the free list have filled ITLs, Oracle will add an extent to the free list. I don't suppose you have MAXTRANS set to 1 or some other very low number? If so, and with concurrent inserts happening, it's possible that you could be allocating more blocks to the free list, even if there are tons of blocks already on the free list, simply due to the ITL shortage. (Note that this is why you'll never see mode 4 TX enqueue waits on insert to a table.) If MAXTRANS isn't really low, and if you're not doing direct-load (APPEND hint) inserts, then I'm stumped. -Mark On Tue, 2002-12-03 at 17:09, Fink, Dan wrote: Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay
RE: ORA-1653: unable to extend table - Why?
No triggers, no snapshots. Thanks for the thougth though. Jay -Original Message- Sent: Tuesday, December 03, 2002 2:34 PM To: Multiple recipients of list ORACLE-L Hum. My last thought would are there triggers or snapshots defined on the table? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/03/2002 08:18 AM Please respond to ORACLE-L Ron, Good idea, but DEGREE=1 There are two indexes, but they are in a different tablespace (which has plenty of free space available and did not give an error). Next extent size is 25M but, as mentioned, it shouldn't have needed a new extent. I'm still at a loss... Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted
RE: ORA-1653: unable to extend table - Why?
freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks
RE: ORA-1653: unable to extend table - Why?
The activity is almost all inserts (there is also some minimal update activity on two CHAR(1) columns later, but it was failing on the inserts). Looking at it, I'm surprised that I have freelists=1 since so much of the activity is insert related. I should probably increase that even if it doesn't help with this problem since it should improve insert performance. I guess the next step is to do a 10046 or statspack report the next time the insert is running. That might not be for a few weeks though (next time I'm certain of is 2nd week of January). It's not in a LMT, but I am using uniform extents. Thanks for all the suggestions, Jay -Original Message- Sent: Wednesday, December 04, 2002 7:49 AM To: Multiple recipients of list ORACLE-L I always overlook things, so I'll just ask and hope it isn't already on the thread: Exactly what kind of activity is going on against this wonderful table? Is this table placed in an LMT? If yes, is it a Uniform LMT? Could you trace the processes/users doing stuff to the table so that we could see if there's contention for ITL slots or other such things? Mogens Mark J. Bobak wrote: Hmm...not sure if this is what's biting you, but if you're out of ITL slots on the block, Oracle will move to the next block on the free list. If all the blocks on the free list have filled ITLs, Oracle will add an extent to the free list. I don't suppose you have MAXTRANS set to 1 or some other very low number? If so, and with concurrent inserts happening, it's possible that you could be allocating more blocks to the free list, even if there are tons of blocks already on the free list, simply due to the ITL shortage. (Note that this is why you'll never see mode 4 TX enqueue waits on insert to a table.) If MAXTRANS isn't really low, and if you're not doing direct-load (APPEND hint) inserts, then I'm stumped. -Mark On Tue, 2002-12-03 at 17:09, Fink, Dan wrote: Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02
RE: ORA-1653: unable to extend table - Why?
MAXTRANS is set to 255. Thanks anyway :) -Original Message- Sent: Wednesday, December 04, 2002 3:54 AM To: Multiple recipients of list ORACLE-L Hmm...not sure if this is what's biting you, but if you're out of ITL slots on the block, Oracle will move to the next block on the free list. If all the blocks on the free list have filled ITLs, Oracle will add an extent to the free list. I don't suppose you have MAXTRANS set to 1 or some other very low number? If so, and with concurrent inserts happening, it's possible that you could be allocating more blocks to the free list, even if there are tons of blocks already on the free list, simply due to the ITL shortage. (Note that this is why you'll never see mode 4 TX enqueue waits on insert to a table.) If MAXTRANS isn't really low, and if you're not doing direct-load (APPEND hint) inserts, then I'm stumped. -Mark On Tue, 2002-12-03 at 17:09, Fink, Dan wrote: Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert
RE: ORA-1653: unable to extend table - Why?
Yes, these are definitely the questions to be asking. And some of the freelist blocks are being written to since we had another insert run night before last and the number of freelist blocks declined somewhat although more extents were also claimed in the new datafile. So sometimes it's writing to the freelist blocks and sometimes it isn't. I'm confused. Jay -Original Message- Sent: Tuesday, December 03, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow
RE: ORA-1653: unable to extend table - Why?
What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't
RE: ORA-1653: unable to extend table - Why?
1895 -Original Message- Sent: Wednesday, December 04, 2002 2:34 PM To: Multiple recipients of list ORACLE-L What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help
RE: ORA-1653: unable to extend table - Why?
Kirti, Thanks for suggesting the Note, I'm reading it now. I tried replying to you directly but my thank you was blocked by your company's spam filter. I'm really curious to know what key word flagged it as spam. Oracle? Jay -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed
RE: ORA-1653: unable to extend table - Why?
I guess this is reason. pct_used and pct_free are very closed. after the deletion if the pct_free is just below 75% it will join the free list. Suppose the block header is Y bytes. And you have pct_free = 10% and pct_used = 75%, this means if a block is 30% free (70% used) it will have (4096 - Y ) * (90 - 70) = less than 800 bytes for insertion before reaching pct_free. And you have the average row length = 1895, so most of the allocations will fail. The problem is very small block size in comparison to row size and high pct_used. For now lower your pct_used. HTH Waleed -Original Message- Sent: Wednesday, December 04, 2002 3:39 PM To: Multiple recipients of list ORACLE-L 1895 -Original Message- Sent: Wednesday, December 04, 2002 2:34 PM To: Multiple recipients of list ORACLE-L What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when
RE: ORA-1653: unable to extend table - Why?
Sorry, I've just seen these stats: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 My message completely contradicts with that fact, so forget about it except if the statistics are wrong. Too weird! let's know what you find. Is it partitioned? What is the extent size? Waleed -Original Message- Sent: Wednesday, December 04, 2002 5:49 PM To: Multiple recipients of list ORACLE-L I guess this is reason. pct_used and pct_free are very closed. after the deletion if the pct_free is just below 75% it will join the free list. Suppose the block header is Y bytes. And you have pct_free = 10% and pct_used = 75%, this means if a block is 30% free (70% used) it will have (4096 - Y ) * (90 - 70) = less than 800 bytes for insertion before reaching pct_free. And you have the average row length = 1895, so most of the allocations will fail. The problem is very small block size in comparison to row size and high pct_used. For now lower your pct_used. HTH Waleed -Original Message- Sent: Wednesday, December 04, 2002 3:39 PM To: Multiple recipients of list ORACLE-L 1895 -Original Message- Sent: Wednesday, December 04, 2002 2:34 PM To: Multiple recipients of list ORACLE-L What is the average row length? Waleed -Original Message- Sent: Wednesday, December 04, 2002 12:49 PM To: Multiple recipients of list ORACLE-L freelist groups is 1 -Original Message- Sent: Tuesday, December 03, 2002 9:59 PM To: Multiple recipients of list ORACLE-L What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all
RE: ORA-1653: unable to extend table - Why?
Ron, Good idea, but DEGREE=1 There are two indexes, but they are in a different tablespace (which has plenty of free space available and did not give an error). Next extent size is 25M but, as mentioned, it shouldn't have needed a new extent. I'm still at a loss... Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
RE: ORA-1653: unable to extend table - Why?
I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K
RE: ORA-1653: unable to extend table - Why?
Hum. My last thought would are there triggers or snapshots defined on the table? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/03/2002 08:18 AM Please respond to ORACLE-L Ron, Good idea, but DEGREE=1 There are two indexes, but they are in a different tablespace (which has plenty of free space available and did not give an error). Next extent size is 25M but, as mentioned, it shouldn't have needed a new extent. I'm still at a loss... Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you
RE: ORA-1653: unable to extend table - Why?
Jay, Don't think it will bypass the freelist there. I don't think the freelist is the cause of the problem here. Richard -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added
RE: ORA-1653: unable to extend table - Why?
Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message
RE: ORA-1653: unable to extend table - Why?
Hmm...Why didn't they ask for your buffer cache hit ratio? Seriously, I've pondered this and it comes down to a question. What would cause a transaction not to use blocks on the freelist? If a transaction cannot use these blocks, then it must allocate new space. If the transaction is set up so as to allocate space above the HWM, we have the same scenario. Are there transactions that are allocating blocks off the freelist? How deep will a transaction read the freelist to find an open block before giving up and allocating space? Are the inserts of such size that they would not fit into the space in the blocks on the freelist? I don't know the answers, but it seems that the questions may offer some clues. I can't wait to find out the real answer! -Original Message- Sent: Tuesday, December 03, 2002 1:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would
RE: ORA-1653: unable to extend table - Why?
Title: RE: ORA-1653: unable to extend table - Why? It just depends on what your definition of it is... -Original Message- From: Miller, Jay [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 12:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1653: unable to extend table - Why? Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free
RE: ORA-1653: unable to extend table - Why?
What is the FREELIST GROUPS for the table? Waleed -Original Message- Sent: Tuesday, December 03, 2002 3:50 PM To: Multiple recipients of list ORACLE-L Just for grins, here's the level of support I'm getting on my Oracle TAR: -- You had stated earlier: 1/ After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. 2/ I've had to add another data file and it has already grown to 600 Meg. If the table is only 600 mb, then there is no way that it can have 8 gb of free space. Since you have a lot of blocks with some free space, you may want to export and import the table back to re-org the table... -- Someone should inform these people that a table can consist of more than one datafile... -Original Message- Sent: Tuesday, December 03, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I had one thought. The Freelist parameter for this table is only set to 1. Is it possible that if it gets tied up with contention for the freelist that it grabs a new extent? I see that some of these blocks are being written to, the num_freelist_blocks is now down to 2095705. But the new data file has grown to 600 Meg. I've opened a TAR to see what Oracle says but I'm not encouraged by the first question they sent me (which was asking to query dba_free_space). Jay -Original Message- Sent: Monday, December 02, 2002 6:14 PM To: Multiple recipients of list ORACLE-L One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original
RE: ORA-1653: unable to extend table - Why?
I was told by the department manager that they were neither using a direct load nor the Append hint. But the developer is back from vacation today so I'll get a more definite answer from him. Thanks, Jay Miller -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L How are the inserts being done? Are you doing an insert with append hint? -Original Message- Sent: Friday, November 29, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Richard Ji 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: Miller, Jay 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: ORA-1653: unable to extend table - Why?
Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert. Any more ideas? -Original Message- Sent: Friday, November 29, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Miller, Jay 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: ORA-1653: unable to extend table - Why?
Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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
RE: ORA-1653: unable to extend table - Why?
One thing I haven't seen mentioned yet is what degree of parallelism is defined for the table? What is the next extent size set to? If the table is paralleled, EACH parallel worker will grab a next extent sized segment. (Been bit by this a few times...) How many indexes and are they in the same tablespace? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan JayMiller@TDWater house.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: ORA-1653: unable to extend table - Why? 12/02/2002 02:04 PM Please respond to ORACLE-L Yep, I agree that coalescing is irrelevant in my current situation. In any event there was no free space until I added the additional datafile but there was the 8gig of space on the freelists. Jay -Original Message- Sent: Friday, November 29, 2002 8:54 PM To: Multiple recipients of list ORACLE-L Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE
RE: ORA-1653: unable to extend table - Why?
My experience yesterday was that dropping an index and trying to rebuild the same index failed ( even after coalescing the tablespace) since we need to wait for SMON to clean up the extents to make them available. I don't know how we make SMON process to coalesce the free space faster enough( or immediately after we delete from the table or dropping an index ). Any ideas? -Original Message- Sent: Monday, December 02, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert. Any more ideas? -Original Message- Sent: Friday, November 29, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Miller, Jay 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: [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).
RE: ORA-1653: unable to extend table - Why?
Check oradebug to wake up smon --- [EMAIL PROTECTED] a écrit : My experience yesterday was that dropping an index and trying to rebuild the same index failed ( even after coalescing the tablespace) since we need to wait for SMON to clean up the extents to make them available. I don't know how we make SMON process to coalesce the free space faster enough( or immediately after we delete from the table or dropping an index ). Any ideas? -Original Message- Sent: Monday, December 02, 2002 3:49 PM To: Multiple recipients of list ORACLE-L Okay, I just heard back from the developer. It was definitely not using either a Direct load or and Append hint. Just a regular insert. Any more ideas? -Original Message- Sent: Friday, November 29, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: Miller, Jay 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: [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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2
Re: ORA-1653: unable to extend table - Why?
Did you insert using direct path ? If so the insert inserts after the highwater mark. The highwater mark is not reinitialized after deletes. So maybe that's why the insert failed. --- Miller, Jay [EMAIL PROTECTED] a écrit : Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Stephane=20Paquette?= 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: ORA-1653: unable to extend table - Why?
did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: ORA-1653: unable to extend table - Why?
How are the inserts being done? Are you doing an insert with append hint? -Original Message- Sent: Friday, November 29, 2002 12:59 PM To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Richard Ji 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: ORA-1653: unable to extend table - Why?
Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: Richard Ji 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: ORA-1653: unable to extend table - Why?
how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: Richard Ji 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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: ORA-1653: unable to extend table - Why?
Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: Richard Ji 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 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
RE: ORA-1653: unable to extend table - Why?
Richard, if pctincrease is zero, and there are a large number of contiguous smaller extents, SMON will not automatically coalesce the tablespace. However, whether or not SMON does an automatic coalesce, if you need an extent that is larger than any of the small ones, Oracle will coalesce those smaller extents to make the one you need. so Jay would not have needed to add a datafile no matter what, if he was not doing a direct path insert. As for meeting in person there is a user group meeting on Dec 12 (check www.nyoug.org for details). You can meet me, and more importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita Bardeen, also of this list. They are all presenting :) I saw Priscilla about a month ago, haven't talked with her since. Rachel --- Richard Ji [EMAIL PROTECTED] wrote: Rachel, What I mean to say is when there are a lot of contiguous smaller free extents. Then coalesce will produce a larger free extent so Jay wouldn't have to add a datafile for his table to grow. On the automatically coalescing part, I believe SMON will only coalesce when pctincrease != 0, or has that changed? My understand could be outdated. With LMT one doesn't have to worry about it. Have a Happy Thanksgiving. PS, I am in New York too, would love to meet you in person some time. Have you talked to Priscilla lately? Richard Ji -Original Message- Sent: Friday, November 29, 2002 5:29 PM To: Multiple recipients of list ORACLE-L how would coalescing help even if there were a lot of smaller free extents? Oracle would do the coalesce automatically, there would be no difference between manually coalescing or allowing Oracle to do it when a new extent was needed. --- Richard Ji [EMAIL PROTECTED] wrote: Coalescing might help if there are many smaller free extents that can be coalesced. But that still doesn't solve Jay's problem. Because he doesn't want the table to extent at all since he just deleted 2 million rows so there are plenty of space within the segment itself. Those free blocks should be used, unless he is doing a direct path insert which will only use space above the HWM. Richard Ji -Original Message- Sent: Friday, November 29, 2002 2:05 PM To: Multiple recipients of list ORACLE-L did u coalesced the tablespaces? -Original Message- Sent: sexta-feira, 29 de Novembro de 2002 17:59 To: Multiple recipients of list ORACLE-L Okay, I can't figure this one out. Earlier this week I got an ORA-1653: unable to extend table on a really big table. However this was just after I had deleted over 2 million rows in the table and we were only inserting 30,000. After reanalyzing the table I saw the following stats in DBA_TABLES: num_freelist_blocks: 2266966 avg_space_freelist_blocks: 3895 Unless I'm misreading this I should have had over 8Gig available for inserts. We tried the insert again and got the same error so I added a datafile and it went through (using about 40Meg of space in the new datafile). Why isn't it making use of the existing blocks on the freelist? Oracle 8.1.7.2 Solaris 2.6 PCTFREE = 10 PCTUSED = 75 Block Size = 4K Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Paulo Gomes 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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
