Re: So nobody can answer this Question - is that it?

First of all, and probably, other people who are frequently answering will agree with me:
I don't like the tone of your subject line. You should keep in mind many of us do this in their spare free time. Many of the questions posted in this newsgroup have answers easily to be found in manuals, at technet.oracle.com, at courses, and we still help those people out. No one of us probably has 7x24 availability, so yes, there are moments the answer to a question is delayed. You are not the only whose questions are not responded, the last four weeks I posted two problems I didn't get any reply on. Any question that needs further consideration and can't be answered immediately has the chance to 'slip'.
Remarks about your question:
1 You don't truncate that table. That will result in: the high water mark of the table not being reset All blocks getting free will need to be placed on the tables free list individually.
As this is no truncate, the delete will be logged and will be subject to normal commit procedures.
As we don't have your delete statement, isn't there a chance an index is being used, and rows are not being deleted in the sequence they are in the table, as no full table scan is used?
If you delete all rows with one statement, why don't you just truncate the table? You probably have reasons for that I don't gather from here. Also the insert might be suffering from reusing existing index slots Once a colleague was doing what you are doing: delete everything first, then insert everything again That usually just blew the index, as space was not reclaimed. Ok, it was Oracle 6, so things may have changed. 2 23 bytes overhead per transaction, so the data space will decrease by 23 bytes for every transaction.
block size minus overhead (row directory, etc etc etc, including transaction slots) = data space.

Hth,

Sybrand Bakker, Oracle DBA

un2000_at_my-deja.com wrote in message <7kdqjt$l1t$1_at_nnrp1.deja.com>...
>I would like to know what really happens when deletes and updates occur
>inside of a data block. I have a table with a single extent and 2555
>rows( Initrans 5, Pctused 10, Pctfree 1 - the reason being that the
>rows will be inserted and subsequently deleted - no updates ).
>When the rows are inserted for the first time, 180 rows can be packed
>into each block( avg rowlen 20,db_block_size 4096, block_overhead 302).
>Every subsequent delete( deletes all 2555 rows - not truncate )and
>insert( inserts 2555 rows ) causes lesser number of rows(176) to be
>placed in each block. This keeps decreasing to 170 and remains stable
>henceforth.
>1. I would like to know why this data space decreases and if so where
>does the data space go - Does the block overhead increase? If so why?
>2. Also, does an increase in the Initrans( and hence the itl slots in
>the row directory in the Row header )affect the data space? I think it
>does.
>I would still like to confirm it.
>(And byw I know what initrans and maxtrans are - I know that an itl slot
>takes up 23 bytes - but that does not answer my question.)
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Fri Jun 18 1999 - 13:06:30 CDT