Oracle-查询表空间使用率

SET LINESIZE 155 PAGESIZE 999 FEEDBACK OFF ECHO OFF VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES

COLUMN status                                   HEADING 'Status'
COLUMN name        FORMAT A25                   HEADING 'Tablespace Name'
COLUMN type        FORMAT a12                   HEADING 'TS Type'
COLUMN extent_mgt  FORMAT a10                   HEADING 'Ext. Mgt.'
COLUMN segment_mgt FORMAT a9                    HEADING 'Seg. Mgt.'
COLUMN ts_size     FORMAT 999,999,999           HEADING 'Tablespace Size'
COLUMN free        FORMAT 999,999,999           HEADING 'Free (in M)'
COLUMN used        FORMAT 999,999,999           HEADING 'Used (in M)'
COLUMN pct_used                                 HEADING 'Pct. Used'

BREAK ON report
COMPUTE SUM label "Total: " OF ts_size used free ON report

SELECT
    d.status                                            status
  , d.tablespace_name                                   name
  , d.contents                                          type
  , d.extent_management                                 extent_mgt
  , d.segment_space_management                          segment_mgt
  , NVL(a.bytes, 0)/1024/1024                           ts_size
  , NVL(f.bytes, 0)/1024/1024                           free
  , NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024         used
  , round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))
                                                        pct_used
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_mgt
  , d.segment_space_management                   segment_mgt
  , NVL(a.bytes, 0)/1024/1024                    ts_size
  , NVL(a.bytes - NVL(t.bytes,0), 0)/1024/1024   free
  , NVL(t.bytes, 0)/1024/1024                    used
  , TRUNC(NVL(t.bytes / a.bytes * 100, 0))       pct_used
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  3,9 desc;