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
And here’s a sample of the output:
REL_FILE_NO BLOCK_NO ROWS_STARTING_IN_BLOCK ----------- ---------- ---------------------- 22 131 199 22 132 199 22 133 199 22 134 199 22 135 88 22 138 111 6 rows selected. ROWS_STARTING_IN_BLOCK BLOCKS ---------------------- ---------- 88 1 111 1 199 4 3 rows selected.
Obviously it could take quite a lot of I/O and CPU to run the two queries against a large table – generally I use it when I want to pick a block to dump afterwards.
Recent comments
2 years 50 weeks ago
3 years 10 weeks ago
3 years 15 weeks ago
3 years 16 weeks ago
3 years 20 weeks ago
3 years 41 weeks ago
4 years 9 weeks ago
4 years 39 weeks ago
5 years 24 weeks ago
5 years 24 weeks ago