Determining the space used by particular tables
Paul, I'm reorganizing a database that has tables and indexes in the same
tablespace. How do I determine the space used by the tables (not
allocated) owned by a particular user with all the table names beginning
with CT_? I want to know what size the file system needs to be where
the tablespace is being created.
You can determine the number of blocks that currently contain data for a table by issuing the following query against the table:
select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from ;
Multiply the result by your db block size to get the number of bytes required.
You can code a PL/SQL block to construct and execute dynamic SQL for each table of interest, summing the results as each table is queried.
This was first published in October 2005