Pasted

 
    
Counting Rows
BY Jonathan Lewis
Here's another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn't DO anything clever, just call routines IN the DBMS_ROWID PACKAGE FOR each ROWID IN the TABLE:
 
 
rem
rem     Rowid_count.SQL
rem     Generic code TO COUNT rows per block IN a TABLE
rem     Ordered BY file AND block
rem
 
define m_table = '&1'
 
spool rowid_count
 
SELECT 
        DBMS_ROWID.rowid_relative_fno(ROWID)    rel_file_no, 
        DBMS_ROWID.rowid_block_number(ROWID)    block_no,
        COUNT(*)                                rows_starting_in_block
FROM 
        &m_table        t1
GROUP BY 
        DBMS_ROWID.rowid_relative_fno(ROWID), 
        DBMS_ROWID.rowid_block_number(ROWID) 
ORDER BY 
        DBMS_ROWID.rowid_relative_fno(ROWID), 
        DBMS_ROWID.rowid_block_number(ROWID)
;
 
 
SELECT
        rows_starting_in_block,
        COUNT(*)        blocks
FROM
        (
        SELECT 
                DBMS_ROWID.rowid_relative_fno(ROWID), 
                DBMS_ROWID.rowid_block_number(ROWID),
                COUNT(*)                                rows_starting_in_block
        FROM 
                &m_table        t1
        GROUP BY 
                DBMS_ROWID.rowid_relative_fno(ROWID), 
                DBMS_ROWID.rowid_block_number(ROWID) 
        )
GROUP BY
        rows_starting_in_block
ORDER BY
        rows_starting_in_block
;
 
spool off