Re: Resizing tables in Oracle 8

Hi Joshua,

normally I cannot imagine why Your table growth and why it get fragmented if You delete all the rows You have inserted. If the content of a data block drops below the pctused table parameter, the block will be appended to the free list. If You insert new rows the go into the blocks on the free list. So normally Your table should only grow to a maximum value. But if You don't delete all the rows Your table will grow if You reorganize it or not.
May be You should try some different values for pctused and also for pctfree. To reduce the maximum size You should set the pctfree to the minimum of 0. But You should only do this if You know that there are no updates on this tables.
The value of pctused depends on the width of Your rows. If You have equally distributed row widths than You can set pctused to a higher value. The maximum value You could choose is 100 times the blocksize minus block header length (~100 - 200 Byte depends on table parameter and block history) minus the row width divided by the blocksize.

    max pctused = 100 * (blocksize - block header length - row width) / blocksize

But I whould expect performance degradation on the inserts if You will choose the
maximum because the blocks may be pop from and to the free list.

--
Best regards

Ralph Ganszky

• The above opinions are mine and do not represent any official
• standpoints of my employer or third party organizations.

Joshua Ford wrote:

> Can anyone tell me how to dynamically resize a table in Oracle 8. I have
> this app that needs to do lots of inserts to specific tables, then massage
> that data send it off to other apps and then delete it. My problem is that
> the table(s) large (results in fragmentation). Truncating the table and
> then exporting and reimporting the table is not an option. Any ideas
>
> -**** Posted from RemarQ, http://www.remarq.com/?a ****-
> Search and Read Usenet Discussions in your Browser - FREE -
Received on Sat Oct 30 1999 - 11:46:47 CDT