set pagesize 500
set lin 120
col TABLESPACE_NAME for a20
col SUM_SPACE(M) for a15
col SUM_BLOCKS for a35
col USED_SPACE(M) for a15
col USED_RATE(%) for a15
col FREE_SPACE(M) for a15


SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'  
          "USED_RATE(%)",  
       FREE_SPACE || 'M' "FREE_SPACE(M)"  
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
UNION ALL                                                           --如果有临时表空间  
SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       USED_SPACE || 'M' "USED_SPACE(M)",  
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
ORDER BY 1;  

查询包含可扩展及非扩展的表空间统计
spool temp.txt
set linesize 500 pagesize 200;
col tablespace_name format a30;
col ip format a30;
col host_name format a15
  SELECT /*+ NO_CPU_COSTING */
        F.HOST_NAME,
         E2.ip,
         d.dbid,
         F.INSTANCE_NAME,
         SYSDATE,
         D.LOG_MODE,
         a.tablespace_name,
         E1.file_count,
         TRUNC (a.total) allocated_space_mb,
         TRUNC (a.total - b.free) Used_mb,
         TRUNC (b.free) free_space_mb,
         ROUND (1 - b.free / a.total, 4) * 100 "USAGE_%",
         c.AUTOSIZE AUTOSIZE_mb,
         ROUND ( (a.total - b.free) / c.AUTOSIZE, 4) * 100 "AUTOUSAGE_%"
    FROM (  SELECT tablespace_name, SUM (NVL (bytes, 2)) / 1024 / 1024 total
              FROM dba_data_files DBA_DATA_FILES1
          GROUP BY tablespace_name) a,
         (  SELECT tablespace_name, SUM (NVL (bytes, 2)) / 1024 / 1024 free
              FROM dba_free_space
          GROUP BY tablespace_name) b,
         (  SELECT tablespace_name, COUNT (file_name) file_count
              FROM dba_data_files DBA_DATA_FILES2
          GROUP BY tablespace_name) E1,
         (  SELECT x.TABLESPACE_NAME, SUM (x.AUTOSIZE) AUTOSIZE
              FROM (SELECT TABLESPACE_NAME,
                           CASE
                              WHEN MAXBYTES / 1024 / 1024 = 0
                              THEN
                                 BYTES / 1024 / 1024
                              ELSE
                                 MAXBYTES / 1024 / 1024
                           END
                              AUTOSIZE
                      FROM DBA_DATA_FILES DBA_DATA_FILES3) x
          GROUP BY x.tablespace_name) c,
         v$database d,
         (SELECT UTL_INADDR.get_host_address () ip FROM DUAL) E2,
         v$instance f
   WHERE     b.tablespace_name = a.tablespace_name
         AND c.TABLESPACE_NAME = b.TABLESPACE_NAME
         AND E1.tablespace_name = a.tablespace_name
         AND a.tablespace_name = c.TABLESPACE_NAME
         AND E1.tablespace_name = b.tablespace_name
         AND E1.tablespace_name = c.TABLESPACE_NAME
ORDER BY 3 DESC;

spool off
exit

版权声明：本文为博主原创文章，未经博主允许不得转载。