column timecol new_value timestamp
set line 160
column dbname new_value db_nm
col TABLESPACE_NAME for a25
select to_char(sysdate,'DDMMYY_HHMISS') timecol
from dual;

select name as dbname from v$database;
spool c:\log\tbs_space_&&db_nm&&timestamp

SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "TOT_SIZE (MB)",
round(SUM(A.SUMB)/1024/1024) "TOT_FREE (MB)",
round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
round(SUM(A.LARGEST)/1024/1024) "MAX_FREE (MB)",SUM(A.CHUNKS) CHUNKS_FREE,
TRUNC(SYSDATE) Today
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
/

prompt ***********************************************************
prompt warning tablespaces less than 15 Perct.. please add space 
prompt ***********************************************************


select * from (
SELECT UPPER(B.INSTANCE_NAME) INSTANCE,
A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "TOT_SIZE (MB)",
round(SUM(A.SUMB)/1024/1024) "TOT_FREE (MB)",
round(SUM(A.SUMB)*100/SUM(A.TOTS)) pct_free,
round(SUM(A.LARGEST)/1024/1024) "MAX_FREE (MB)",SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME)
where pct_free <= 15
order by 5
/

spool off