gettsusage 表空间使用率

set lines 200 pages 200 long 100000

SELECT d.status "Status",

       d.tablespace_name "Name",

       d.contents "Type",

       d.extent_management "Extent Management",

       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.9999') "Total Size (M)",

       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,

               '99999999.999') "Used (M)",

       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') "Free (M)",

       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),

               '990.00') "Used %",

       sysdate "日期"

  FROM sys.dba_tablespaces d,

       (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_data_files

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = f.tablespace_name(+)

   AND NOT

        (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')

UNION ALL

SELECT d.status "Status",

       d.tablespace_name "Name",

       d.contents "Type",

       d.extent_management "Extent Management",

       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",

       to_char(nvl(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",

       to_char((nvl(a.bytes / 1024 / 1024, 0)) -

               (nvl(t.bytes, 0) / 1024 / 1024),

               '99999999.999') "Free (M)",

       to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used %",

       sysdate "日期"

  FROM sys.dba_tablespaces d,

       (SELECT tablespace_name, SUM(bytes) bytes

          FROM dba_temp_files

         GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM(bytes_cached) bytes

          FROM v$temp_extent_pool

         GROUP BY tablespace_name) t

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = t.tablespace_name(+)

   AND d.extent_management LIKE 'LOCAL'

   AND d.contents LIKE 'TEMPORARY'

 ORDER BY "Used %" DESC;