This is a script I wrote some time ago. It used to run at regular interval for all of my databases. I still use it daily. This allows me to follow up the tablespace used space of any database. Note it takes in account whether the data files are autoextensible or not. Data files which are autoextensible is something I dislike. If I use this feature I always configure a maxsize.
This scripts selects from dba_segments and dba_data_files. Database block fragmentation and free space under the segments high water mark is not detected with it.
column tablespace_name format a30 column "USED_SPACE" format 999999.99 column "ALLOCATED_SPACE" format 999999.99 column "ALLOWED_MAX_SPACE" format 999999.99 spool used_space select a.tablespace_name,round(sum(a.bytes)/(1024*1024),2) "USED_SPACE", (select sum(b.bytes)/(1024*1024) from dba_data_files b where a.tablespace_name=b.tablespace_name group by b.tablespace_name) "ALLOC_SPACE", (select sum(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024))) from dba_data_files b where a.tablespace_name=b.tablespace_name group by b.tablespace_name) "MAX_SPACE", round(( (select sum(c.bytes)/(1024*1024) from dba_segments c,dba_tablespaces d where c.tablespace_name=d.tablespace_name and a.tablespace_name=d.tablespace_name group by d.tablespace_name)*100/ (select sum(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024))) from dba_data_files b where a.tablespace_name=b.tablespace_name group by b.tablespace_name) ),0) "PCT" from dba_segments a group by a.tablespace_name order by a.tablespace_name /
select a.file_id,a.bytes_used/(1024*1024) "HWM_TEMP",
a.bytes_used/(1024*1024) + a.bytes_free/(1024*1024) "ALLOC_TEMP",
round(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024)),2) "MAX_TEMP"
from v$temp_space_header a, dba_temp_files b
where a.file_id=b.file_id
order by a.file_id;
spool off;
I post a sample output, note I hide the real names of the tablespaces for security reasons.
TABLESPACE_NAME USED_SPACE ALLOC_SPACE MAX_SPACE PCT --------------------------- ---------- ----------- ---------- ------ TS1 81.25 200 2000 4 TS2 1687.00 2048 4000 42 TS3 305.00 400 2000 15 TS4 187.13 500 2200 9 SYSAUX 882.38 900 2048 43 SYSTEM 704.88 900 2048 34 UNDOTBS2 14.25 400 2048 1 TS5 41.75 100 2000 2 TS6 120.00 150 2000 6 TS7 25.25 100 2000 1 TS8 66.50 100 2000 3 11 rows selected.