I have a table that I have partitioned into months(12 months). Each partition is in a separate tablespace. So I have 12 partitons and 12 tablespaces.

I ran a query and the query tells me that 60% of the tablespace for march has been used.
But when I select count(*) from march partiton, it did not return any rows(ZERO).Meaning there is no data in the march partition

My question is, if the tablespace is supposed to contain only the partition for the month of march, why is it that the tablespace usage for march is 60% and when I select count(*) from march partitions it returns no rows.

Can anyone explain.
This is realy realy realy bugging my mind.

THIS IS THE SCRIPT THAT TELLS ME THE TABLESPACE UTILIZATION:

select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",round((f.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name


can anybody explain what is going on here?



regards,
charity