The following bit of SQL will do the trick:

SELECT TABLESPACE_NAME,
ROUND(SUM(TOTAL_MB)-SUM(FREE_MB)) MB_USED,
ROUND(SUM(TOTAL_MB)) MB_SIZE,
ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(TOTAL_MB)*100) PCT_FULL,
ROUND(SUM(MAX_MB) -(SUM(TOTAL_MB)-SUM(FREE_MB))) MB_FREE,
ROUND(SUM(MAX_MB)) MB_MAXSIZE,
ROUND((SUM(TOTAL_MB)-SUM(FREE_MB))/SUM(MAX_MB)*100) PCT_UTIL
FROM
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,0 TOTAL_MB,0 MAX_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,0 CURRENT_MB,SUM(BYTES)/1024/1024 TOTAL_MB,
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/1024/1024 MAX_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)
GROUP BY TABLESPACE_NAME;

You'll get a report that looks a bit like this:

TABLESPACE_NAME MB_USED MB_SIZE PCT_FULL MB_FREE MB_MAXSIZE PCT_UTIL
 

INDX	0	25	0	16384	16384	0
SYSTEM	87	325	27	16297	16384	1
UNDOTBS	1	200	1	16383	16384	0
USERS	0	25	0	16384	16384	0

The various columns can be explained thus:

MB_USED: Total amount of space currently in use within a tablespace MB_SIZE: Total size of the tablespace, adding up the physical sizes of all datafiles PCT_FULL: Used divided by Size, expressed as a percentage MB_FREE: Total amount of tablespace still sitting there empty MB_MAXSIZE: This shows how big the tablespace can become. Often this will be the same as the MB_SIZE column -but not if you've switched on autoextend, in which case this column will show the maxsize parameter PCT_UTIL: Size divided by Maxsize expressed as a percentage.

In other words, PCT_FULL shows you what percentage of what you've currently got is being used, whereas PCT_UTIL shows what percentage of the maximum you could possibly *one day* have is being used. Personally, I wouldn't touch autoextend with a barge pole, and I'd be looking to resize or add extra datafiles when the PCT_FULL column is reaching around the 75 - 80% mark.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips