Finding out size of each table in a tablespace?

Question

I want to find out the size of all the tables in a particular tablespace, in oracle 10g

The O/P should specify the size currently being occupied by each table. It should include the size of a blob field if there is any in the table, also it should include the size of any index if on that table.

I am currently using this query but it does not include the size of the index and also am not sure if this includes the blob size.

select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) ROW_SIZE_KB, (BLOCKS * 8)  BLOCK_SIZE_KB  
from USER_TABLES  
order by TABLE_NAME

Suggestions..

It will be great if some one can write some query which gives the size of each field in a table , also the total size of the table and size of each index if any on the table.

EDIT : What I actually want : I want to know the actual space being consumed by the table, the space actually being consumed by my data. not the empty space. and if there are any indexes then the size of the index, not the extra bytes which oracle keeps for future usage.

motive : I have moved a few databases from oracle to SqlServer 2008 Using SSMA . The problem I am facing is that the size of databases has reduced from 80GB(in oracle) to 20 GB(in SqlServer). I used this query:

SELECT
  /* + RULE */
  df.tablespace_name "Tablespace",
  df.bytes / (1024*1024*1024) "Size(GB)",
  SUM(fs.bytes) / (1024*1024*1024) "Free(GB)",
  NVL(ROUND(SUM(fs.bytes) * 100 / df.bytes),1) "%Free",
  ROUND((df.bytes         - SUM(fs.bytes)) * 100 / df.bytes) "%Used"
FROM dba_free_space fs,
  (SELECT tablespace_name,
    SUM(bytes) bytes
  FROM dba_data_files
  WHERE tablespace_name not like 'SCN%'
  GROUP BY tablespace_name
  ) df
WHERE fs.tablespace_name (+) = df.tablespace_name 
GROUP BY df.tablespace_name, df.bytes
order by 3 desc

Now, what I was thinking of doing is that since there is this reduction, I thought of comparing the data in table under the tablespace. Thats why the question .

Solution

To get total allocated space.

select bytes from user_segments where segment_name = 'TABLE_NAME';

Repeat for each index. This will give you space that is allocated including space reserved for future inserts and updates.

Comments

[Coxy]

This script doesn't do anything.