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: mln_at_miracleas.dk

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: ListGuru_at_fatcity.com (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).