Oracle-01.DBA脚本--表空间查询

目前计算表空间最准确的脚本，考虑了多种潜在的情况。

prompt
prompt "---------------------------------------"
prompt "Tablespace  Total Info"
prompt "---------------------------------------"

set linesize 160 pagesize 1000
col TABLESPACE_NAME for a25
col FREE_PCT for a10;
compute sum of Total_MB on report;
compute sum of MAX_MB on report;
break on report
col MAX_FREE_PCT for a15;
select /* + rule */
 b.TABLESPACE_NAME,
 b.CONTENTS,
 a.TOTAL_MB,
 a.USED_MB,
 a.FREE_MB,
 a.FREE_PCT,
 a.MAX_MB,
 a.MAX_FREE_MB,
 a.MAX_FREE_PCT,
 b.STATUS
  from (SELECT total.tablespace_name,
               Round(total.MB) AS Total_MB,
               Round(free.MB) AS Free_MB,
               Round(total.MB - free.MB) AS Used_MB,
               Round((free.MB / total.MB) * 100) || '%' AS Free_Pct,
               Round(total.MAX_MB) AS MAX_MB,
               Round(total.MAX_MB - (total.MB - free.MB)) AS MAX_FREE_MB,
               Round((total.MAX_MB - (total.MB - free.MB)) / total.MAX_MB * 100) || '%' AS MAX_FREE_PCT
          FROM (SELECT tablespace_name,
                       sum(bytes) / 1024 / 1024 AS MB
                  FROM dba_free_space
                 GROUP BY tablespace_name) free,
               (SELECT tablespace_name,
                       sum(DECODE(AUTOEXTENSIBLE,'YES',greatest(MAXBYTES,BYTES),BYTES)) / 1024 / 1024 AS MAX_MB,
                       sum(BYTES) / 1024 / 1024 AS MB
                  FROM dba_data_files
                 GROUP BY tablespace_name) total
         WHERE free.tablespace_name = total.tablespace_name
		 union all
		 SELECT total.tablespace_name,
       Round(total.MB) AS Total_MB,
       Round(total.MB - used.MB) AS Free_MB,
       Round(used.MB) AS Used_MB,
       Round(((total.MB-used.MB) / total.MB) * 100) || '%' AS Free_Pct,
       Round(total.MAX_MB) AS MAX_MB,
       Round(total.MAX_MB - used.MB) AS MAX_FREE_MB,
       Round((total.MAX_MB - used.MB) / total.MAX_MB * 100) || '%' AS Free_Pct_Max
  FROM (select ss.tablespace_name,
               sum((ss.used_blocks * ts.blocksize)) / 1024 / 1024 mb
          from gv$sort_segment ss, sys.ts$ ts
         where ss.tablespace_name = ts.name
         group by ss.tablespace_name) used,
       (SELECT tablespace_name,
               sum(DECODE(AUTOEXTENSIBLE,'YES',greatest(MAXBYTES,BYTES),BYTES)) / 1024 / 1024 AS MAX_MB,
               sum(BYTES) / 1024 / 1024 AS MB
          FROM dba_temp_files
         GROUP BY tablespace_name) total
 WHERE used.tablespace_name = total.tablespace_name
) a,
       dba_tablespaces b
 where a.tablespace_name(+) = b.tablespace_name;` 
复制