Segment space management issues

Hi,
I have a theoretical doubt with regards to manually managing segment space. There r 3 parameters, PCTFREE, PCTUSED and FREELIST. Data can be inserted into a segment upto a point where we reach the PCTFREE parameter value. also when we want to insert data into a segment already with some data existing, we can only insert data if the existing data is occupying space below the PCTUSED parameter value. So once a segment has data lesser than the PCTUSED value, then this segment is put into the freelist for insertion. my question is, once a segment is in the free list, can i be allowed to delete data from that segment?
It would be nice if someone cud answer this question.

thanks,
Jay

Quote:

---

Originally Posted by bjayaram

my question is, once a segment is in the free list, can i be allowed to delete data from that segment?
It would be nice if someone cud answer this question.

---

Not sure I understand this? Just because a block is on the freelist doesn't mean you can't delete from that block.

The block will remain on the freelist until it reaches the PCTFREE limit. Once it reaches the PCTFREE, the block will be taken off the freelist. If rows are deleted in the block or rows decrease in size due to updates and the freespace falls below PCTUSED, then the block is put back on the freelist.

Have you seen this How PCTFREE and PCTUSED Work Together.

If possible, use ASSM and you don't have to worry about this.

Quote:

---

Originally Posted by ebrian

Not sure I understand this? Just because a block is on the freelist doesn't mean you can't delete from that block.

The block will remain on the freelist until it reaches the PCTFREE limit. Once it reaches the PCTFREE, the block will be taken off the freelist. If rows are deleted in the block or rows decrease in size due to updates and the freespace falls below PCTUSED, then the block is put back on the freelist.

Have you seen this How PCTFREE and PCTUSED Work Together.

If possible, use ASSM and you don't have to worry about this.

---

thanks a lot.. I have one more doubt... what is the need to use the PCTUSED parameter then? why do i need to go all the way down to 39% in order to insert assuming my PCTUSED = 40.

Typically, PCTUSED is relevant only in tables that undergo deletes. You may be able to pack rows into blocks more tightly by setting PCTUSED to be higher than 40%.

For tables with many inserts, changing PCTUSED may improve block storage performance. Blocks that are densely populated can cause freelist contention, but fewer blocks are required, tables are smaller, and read operations are faster.

i did not understand this... can you please explain with an example?

This may help explain it more Setting PCTFREE and PCTUSED Based on Average Row Length.