Percentage used of Tablespaces

I regulary have disucssions with colleagues what is the used percentage of a tablespace in an Oracle Database, my reply is normally "it depends :-)". Most of people rely on tools like Grid Control and so on, but these tools are showing only the percentage usage with a scope of the moment.
Lets assume we have a tablespace with one datafile. The datafile is created with an initial size of 100 MB and maxsize of 1024 MB. Now we fill up the datafile with some tables which all together are using 90 MB. So the normal tools will show us that the tablespace is used for 90%. Thats correct for the moment, but we have to consider that the datafile can grow until 1024 MB. When we consider this we come to:
 (90 MB / 1024 MB) * 100 = 8.78 %

A percentage used of 90% versus 8.78% sounds a little bit different :-)

Below is a small script, which consider the maxsize option of datafiles for a tablespace, so that you can see the actual percenatge usuage and the percentage usage for the possible maxsize:

set linesize 200
col name format a20

select (select tablespace_name
from dba_tablespaces
where tablespace_name = b.tablespace_name
) name
,round(kbytes_alloc/1024, 2) mbytes
,round((kbytes_alloc-nvl(kbytes_free,0))/1024, 2) used
,round(nvl(kbytes_free,0)/1024, 2) free
,round(((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100, 2) pct_used
,round(nvl(largest,0)/1024, 2) largest
,round(nvl(kbytes_max,kbytes_alloc)/1024, 2) max_size
,round(decode(kbytes_max,0,0,((kbytes_alloc-nvl(kbytes_free,0))/kbytes_max)*100),2) pct_max_used
,(select extent_management
from dba_tablespaces
where tablespace_name = b.tablespace_name) extent_management
,(select segment_space_management
from dba_tablespaces
where tablespace_name = b.tablespace_name) segment_space_management
from (select sum(bytes)/1024 Kbytes_free, max(bytes)/1024 largest, tablespace_name
from sys.dba_free_space
group by tablespace_name ) a
,(select sum(bytes)/1024 Kbytes_alloc, sum(maxbytes)/1024 Kbytes_max, tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc, sum(maxbytes)/1024 Kbytes_max, tablespace_name
from sys.dba_temp_files group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 2;

And the result should look like as follows:

NAME         MBYTES     USED       FREE       PCT_USED   LARGEST    MAX_SIZE   PCT_MAX_USED  EXT_MANAGE  SEGMENT_SPACE
------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------------ ----------
TOOLS        10         .062       9.93       .62        9.93       65535.96    0            LOCAL         AUTO
DRSYS        20         12.56      7.43       62.81      7.18       65535.96    .02          LOCAL         AUTO
USERS        25         7.06       17.93      28.25      17.93      65535.96    .01          LOCAL         AUTO
INDX         25         .06        24.93      .25        24.93      65535.96    0            LOCAL         AUTO
XDB          103.75     103.5      .25        99.75      .25        65535.96    .16          LOCAL         AUTO
TEMP         256        256        0          100        0          0           0            LOCAL         MANUAL
UNDOTBS1     636        85.31      550.68     13.41      189.93     32768       .26          LOCAL         MANUAL
SYSAUX       1024       480.68     543.31     46.94      502.87     1024        46.94        LOCAL         AUTO
SYSTEM       1024       675.31     348.68     65.94      347.93     65535.96    1.03         LOCAL         MANUAL

Under the column PCT_MAX_USED you can see the percentage usage in consideration with the maxsize of the datafile(s) and under the column PCT_USED you can see the actual percentage usage calculated according to the actual datafile(s) size.