Rows in a block ?

# Thread: Rows in a block ?

1. Senior Member
Join Date
Feb 2002
Posts
267
Is it possible to find out how many rows are
stored inside a block.

Regards
sonia

Join Date
Oct 2001
Location
Posts
763
Some time ago, I think was Jurij who post this formula:

For an object:

rows_in_a_block=space_capacity_for_a_block/estimation_of_the_row_lengh

Hope that helps

Angel

3. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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.

4. Senior Member
Join Date
Aug 2000
Location
Belgium
Posts
342
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);

5. Junior Member
Join Date
Aug 2002
Posts
21
jmodik
will ya be kind enough to tell me how 2 find rowid of a block????

6. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
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.

7. Junior Member
Join Date
Aug 2002
Posts
21
I have till now not understood how to get no of rows in ablock can anybody give a soln;

8. Senior Member
Join Date
Aug 2000
Location
Belgium
Posts
342
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•