How many rows there are in each block of a table?

Is very easy know how many rows fit into each block and very easy to check. lets see example:

We have 8 KB blocks

sho parameter db_block_size

NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192

Now we create a 1000 rows table called TEST

CREATE TABLE test AS
SELECT level id, ‘nom_’||level nombre
FROM dual
CONNECT BY level <= 1000;

To see the number of blocks needed to store 1000 records and the number of records in each of these blocks, we can run the following query.
SELECT dbms_rowid.rowid_block_number(rowid) “Block number”, count(*)
FROM test
GROUP BY dbms_rowid.rowid_block_number(rowid)
ORDER BY dbms_rowid.rowid_block_number(rowid) ASC;

Block number COUNT(*)
———— ———-
46196 438
46197 425
46198 137
With this query we can check, in block 46196 we have 438 rows, in block 46197 we have 425 rows, bt, What can we do to know if this is correct?

To check if this info is true we are go to exec a dump of the 3 blocks and check trace info
First we need find datafile number where our table(segment) is storage.

SELECT header_file FROM dba_segments WHERE segment_name = ‘TEST’;

HEADER_FILE
———–
4

alter system dump datafile 4 block min 46196 block max 46198;

 

select spid
from v$session s, v$process p
where p.addr = s.paddr
and s.audsid = sys_context(‘userenv’,’sessionid’)
/

SPID
————
4360
A trace file was generated where parameter user_dump_dest points. Name of trace test_ora_4360.trc (number is SPID)
lets read the important file parts:

data_block_dump,data header at 0x4f06a7c
===============
flag=——–

nrow=438 <– total rows number of block 46196.

0xe:pti[0] nrow=438 offs=0 <– there are 438 rows in blcok 46196 startting from roiw number 0.

data_block_dump,data header at 0xa206a7c
===============
flag=——–
nrow=425 <– total rows number of block 46197.
frre=-1

0xe:pti[0] nrow=425 offs=0 <– there are 425 rows in blcok 46197 startting from roiw number 0.

data_block_dump,data header at 0xa206a7c
===============
tsiz: 0x1f80
flag=——–
nrow=137 <– total rows number of block 46198.
0xe:pti[0] nrow=137 offs=0 <– there are 137 rows in blcok 46198 startting from roiw number 0.

With this example we could check and verify the result of our first query 🙂

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s