Rows in a block ?

Is it possible to find out how many rows are
stored inside a block.

Regards
sonia

Some time ago, I think was Jurij who post this formula:

For an object:

header_block~=120 bytes
space_capacity_for_a_block=(block_size-header_block)(1-pctfree/100)

rows_in_a_block=space_capacity_for_a_block/estimation_of_the_row_lengh

Hope that helps

Angel

Determine which table this block belongs to by querying dba_extents. Then isue:

select count(*) from that_table where rowid = my_rowid;

my_rowid is the rowid of the block you are interested in.

In 9i, you can use :

ROWID_BLOCK_NUMBER Function
This function returns the database block number for the input ROWID.

Syntax
DBMS_ROWID.ROWID_BLOCK_NUMBER (
row_id IN ROWID)
RETURN NUMBER;

Pragmas
pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);

Parameters
Table 62-7 ROWID_BLOCK_NUMBER Function Parameters
Parameter Description
row_id
ROWID to be interpreted.


Example
The example SQL statement selects the block number from a ROWID and inserts it into another table:

INSERT INTO T2 (SELECT dbms_rowid.rowid_block_number(ROWID)
FROM some_table
WHERE key_value = 42);

jmodik
will ya be kind enough to tell me how 2 find rowid of a block????

Quote:

---

Originally posted by sm8728
jmodik
will ya be kind enough to tell me how 2 find rowid of a block????

---

Ignore my previous post, my brains were not functioning at that time - it was before my first coup of coffe.

I have till now not understood how to get no of rows in ablock can anybody give a soln;

Something like this ?

select dbms_rowid.ROWID_BLOCK_NUMBER(ROWID), count(*) NumberOfRowsInThisBlock
from Mytable
group by dbms_rowid.ROWID_BLOCK_NUMBER(ROWID)

But, this will not take into account migrated/chained rows.

Gert