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.