Listing 1: Sample Table T Blocks and Rows
SQL< create table t ( x int, y char(2000) );
Table created.
SQL< begin
2 for i in 1 .. 102
3 loop
4 insert into t values ( i, 'x' );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL< select min(cnt), max(cnt), avg(cnt), count(*)
2 from (select dbms_rowid.ROWID_BLOCK_NUMBER( rowid ), count(*) cnt
3 from t
4 group by dbms_rowid.ROWID_BLOCK_NUMBER( rowid )
5 )
6 /
MIN(CNT) MAX(CNT) AVG(CNT) COUNT(*)
-------- -------- -------- --------
3 3 3 34
This shows that I have three rows per block (and rows are 2K each and block size is 8K).
The following query returns the rowid block number for each row:
SQL< select dbms_rowid.rowid_block_number(rowid), x
2 from t
3 where x <= 7;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) X
------------------------------------ -----------
2058 1
2058 2
2058 3
2059 4
2059 5
2059 6
2060 7
7 rows selected.
|