ORACLE 检查表空间sql

set linesize 110
col tablespace_name format a15
SELECT   c.tablespace_name, ROUND (a.BYTES / 1048576, 2) megs_alloc,
         ROUND (b.BYTES / 1048576, 2) megs_free,
         ROUND ((a.BYTES - b.BYTES) / 1048576, 2) megs_used,
         ROUND (b.BYTES / a.BYTES * 100, 2) pct_free,
         ROUND ((a.BYTES - b.BYTES) / a.BYTES, 2) * 100 pct_used,
         ROUND (maxbytes / 1048576, 2) MAX
    FROM (SELECT   tablespace_name, SUM (a.BYTES) BYTES,
                   MIN (a.BYTES) minbytes,
                   SUM (DECODE (a.autoextensible,
                                'YES', a.maxbytes,
                                'NO', a.BYTES
                               )
                       ) maxbytes
              FROM SYS.dba_data_files a
          GROUP BY tablespace_name) a,
         (SELECT   a.tablespace_name, NVL (SUM (b.BYTES), 0) BYTES
              FROM SYS.dba_data_files a, SYS.dba_free_space b
             WHERE a.tablespace_name = b.tablespace_name(+)
                   AND a.file_id = b.file_id(+)
          GROUP BY a.tablespace_name) b,
         SYS.dba_tablespaces c
   WHERE a.tablespace_name = b.tablespace_name(+)
         AND a.tablespace_name = c.tablespace_name
ORDER BY 1
;
prompt about temp usage:
select s.TABLESPACE_NAME, t.CONTENTS,
  d.total/1024/1024 "Total (M)",
  s.USED_BLOCKS*4096/1024/1024 "Used (M)",
  s.USED_BLOCKS*4096/d.total "% Used"
from v$sort_segment s, dba_tablespaces t,
  (
    select tablespace_name, sum(bytes) total from dba_data_files group by tablespace_name
        union all
        select tablespace_name, sum(bytes) total from dba_temp_files group by tablespace_name
   ) d
where s.TABLESPACE_NAME=t.TABLESPACE_NAME
and s.tablespace_name=d.tablespace_name
;