Unanswered: Calculate table free/used space in 9i rel. 2
We're using Oracle 9i Rel. 2. All of our tablespaces are using LMT and ASSM.
From time to time, there will be row deletions. How do we then calculate the actual space being used by a table. I'm not talking about space that's allocated to the table, but actual space usage within the table.
We tried using DBMS_SPACE.SPACE_USAGE, but I don't think it's what we're looking for or perhaps we're misinterpretting the output.
ORACLE tags each record with a unique rowid that contains the datafile,the block number, and the rownumber. To find out how much space a table actually uses, count the number of unique blocks in that table's rowid.
Since a single table could span multiple database files and the same block number could show up in both files, count the number of distinct block/file combinations:
select count(distinct(substr(rowid,1,8)||substr(rowid,15, 4)))
It will get you within 5% of reality, which is close enough for what you need.