sqlplus -s / <<-EOT
set pages 50
set line 100
set verify off
set feed off
set trimspool on
break on report
column FREE for 9,999,999 heading "%Free"
column PCT_USED for 9,999 heading "%Used"
column tablespace_name for a18 heading "Tablespace"
column TOT_SIZE for 9,999,999 heading "Size"
column TOT_FREE for 9,999,999 heading "Free"
column TOT_USED for 9,999,999 heading "Used"
column MAX_BYTES for 999,999 heading "Lrgst|Chunk"
column COUNTF     for 9,999 heading "Count"

repheader left "PAGE Threshold Exceeded            Tablespace Free And Used Analysis"
select a.tablespace_name, TOT_SIZE, TOT_FREE, (TOT_SIZE -  TOT_FREE) TOT_USED,
       Round((TOT_FREE/TOT_SIZE)*100) as FREE,
         Round((1-(TOT_FREE/TOT_SIZE))*100) PCT_USED,page_threshold threshold, max_bytes,countf
    from (select tablespace_name, round(sum(bytes)/1048576) TOT_SIZE
            from dba_data_files group by tablespace_name) a,
         (select tablespace_name, round(sum(bytes)/1048576) TOT_FREE
           from dba_free_space group by tablespace_name) b,
       (select tablespace_name,round(max(bytes)/1048576)  MAX_BYTES,count(*)COUNTF
            from dba_free_space group by tablespace_name) c,
       (select tablespace_name from dba_tablespaces where
          contents != 'UNDO') d,
       (select tablespace_name,page_threshold from tsused_thresholds) e
  where a.tablespace_name = b.tablespace_name
 and   b.tablespace_name = c.tablespace_name
 and   c.tablespace_name = d.tablespace_name
 and   c.tablespace_name = e.tablespace_name
 order by pct_used,a.tablespace_name
/
EOT

PAGE Threshold Exceeded            Tablespace Free And Used Analysis
                                                                                    Lrgst
Tablespace               Size       Free       Used      %Free  %Used  THRESHOLD    Chunk  Count
------------------ ---------- ---------- ---------- ---------- ------ ---------- -------- ------
MODELER                   500        499          1        100      0         90      496      2
IE16K                     100         99          1         99      1         90       68      4
DE1M                      550        439        111         80     20         90      417      2
TOOLS                   2,500      1,845        655         74     26         90    1,194    445
USR_100M               44,000     28,600     15,400         65     35         90    2,780    218
USERS                   5,024      3,135      1,889         62     38         90    3,000      3
DE16K                     450        241        209         54     46         90      162      7
IE1M                    1,000        497        503         50     50         90      497      1
SYSAUX                 45,448     22,314     23,134         49     51         80    3,968    580
SYSTEM                  1,331        639        692         48     52         90      639      3
DE10M                  30,504     13,840     16,664         45     55         90    6,260     95
USR_10M                16,000      6,450      9,550         40     60         90    3,190      6
USR_1M                    500        202        298         40     60         90      202      1
IE10M                  21,504      7,330     14,174         34     66         90    2,960     52
PERF_STAT_DATA          1,500        512        988         34     66         90      484      3
IE100M                226,304     61,200    165,104         27     73         90   16,700     92
MODELER_1             193,784     41,910    151,874         22     78         90   27,130      3
DE100M                860,160    167,000    693,160         19     81         90   28,000     49