Oracle Database Discussions

I have a few tables that are partitioned by month. All partitons for a specific month are all in the same tablespace for index and data seperately.

When I view the space used by tablespaces, I see that the future partitions which have no data in them show space used. How is this possible.? For the partitons tablespaces, There is only data in FACT_DATA_200709 and FACT_INDEX_200709. As you can see all the other partition tablespaces also show used space?


SELECT t.name TSName,
round(v.bytes/(1024*1024)) TotalSize,
(round(v.bytes/(1024*1024)) - nvl(round(SUM(f.bytes)/(1024*1024)), 0)) UsedSpace,
v.files NoFiles
FROM (
SELECT ts#, SUM(bytes) bytes, count(file#) files
FROM v$datafile
GROUP BY ts#
) v,
dba_free_space f,
sys.ts$ t
WHERE t.name = f.tablespace_name(+)
AND t.ts# = v.ts#
GROUP BY v.ts#, t.name, v.bytes, v.files
ORDER BY t.name

TSNAME TOTALSIZE USEDSPACE NOFILES
------------------------------ ---------- ---------- ----------
DIM_DATA 176 64 1
DIM_INDEX 253 171 1
FACT_DATA 2000 700 1
FACT_DATA_200709 12500 1200 1
FACT_DATA_200710 1300 900 1
FACT_DATA_200711 1300 900 1
FACT_DATA_200712 1300 900 1
FACT_DATA_200801 1300 900 1
FACT_DATA_200802 1300 900 1
FACT_DATA_200803 1000 90 1
FACT_DATA_200804 1000 90 1

TSNAME TOTALSIZE USEDSPACE NOFILES
------------------------------ ---------- ---------- ----------
FACT_DATA_200805 1300 900 1
FACT_DATA_200806 1000 90 1
FACT_DATA_200807 1000 90 1
FACT_DATA_200808 1000 90 1
FACT_DATA_200809 1000 90 1
FACT_DATA_200810 1300 900 1
FACT_DATA_MAX 29 9 1
FACT_INDEX 18200 2500 1
FACT_INDEX_200709 13700 1600 1
FACT_INDEX_200710 1500 1500 1
FACT_INDEX_200711 1500 1500 1

TSNAME TOTALSIZE USEDSPACE NOFILES
------------------------------ ---------- ---------- ----------
FACT_INDEX_200712 1500 1500 1
FACT_INDEX_200801 1500 1500 1
FACT_INDEX_200802 1500 1500 1
FACT_INDEX_200803 1000 75 1
FACT_INDEX_200804 1000 75 1
FACT_INDEX_200805 1500 1500 1
FACT_INDEX_200806 1000 75 1
FACT_INDEX_200807 1000 75 1
FACT_INDEX_200808 1000 75 1
FACT_INDEX_200809 1000 75 1
FACT_INDEX_200810 1500 1500 1

TSNAME TOTALSIZE USEDSPACE NOFILES
------------------------------ ---------- ---------- ----------
FACT_INDEX_MAX 15 15 1
SYSAUX 1000 447 1
SYSTEM 1000 372 1
UNDO2 2000 29 1