set verify off linesize 100
ACCEPT vLIMIT NUMBER DEFAULT 0 PROMPT 'TABLESPACE USAGE PERCENT >= : '
COL TODAY FORMAT A20 NEW_VALUE CURR_TIME
COL TODAY NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS' ) TODAY FROM DUAL;
PROMPT ========================================================
PROMPT ==  FREE SPACE USAGE PCT% FOR EACH TABLESPACE
PROMPT => DATE : [ &CURR_TIME ]
PROMPT ========================================================
COL TABLESPACE_NAME FORMAT A13
COL FILE_NAME FORMAT A30
COL FREE_MB  FORMAT 999,999,999
COL TOT_MB   FORMAT 999,999,999
COL "USAGE%" FORMAT 999.9
SELECT A.TABLESPACE_NAME,
                        SUM(A.BYTES)/(1024*1024) AS TOT_MB,
                        NVL(SUM(SZ_MB),0) AS FREE_MB,
       NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) AS "USAGE%"
FROM DBA_DATA_FILES A,
  ( SELECT FILE_ID  , SUM(BYTES)/(1024.*1024.) AS SZ_MB
    FROM DBA_FREE_SPACE
    GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID (+)
GROUP BY A.TABLESPACE_NAME
HAVING NVL(( 1. - SUM(SZ_MB)/(SUM(A.BYTES)/(1024.*1024.)) ) * 100,100) >= &vLIMIT
;