oracle 查看数据文件大小与使用率方法

SELECT d.file_name,
       TO_CHAR((d.bytes / 1024 / 1024), '99999990.000'),
       NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'),
           TO_CHAR((d.bytes / 1024 / 1024), '99999990.000')),
       d.file_id,
       d.autoextensible,
       d.increment_by,
       d.maxblocks
  FROM sys.dba_data_files d,
       (SELECT file# file_id, SUM(e.length * ts.blocksize) bytes
          FROM sys.fet$ e, sys.ts$ ts
         WHERE ts.ts# = e.ts#
           AND ts.name = 'USERS'
         GROUP BY file#
        UNION ALL
        SELECT file_id, SUM(e.blocks * ts.blocksize) bytes
          FROM sys.dba_lmt_free_space e, sys.ts$ ts
         WHERE ts.ts# = e.tablespace_id
           AND ts.name = 'USERS'
         GROUP BY file_id) s
 WHERE (s.file_id(+) = d.file_id)
   AND (d.tablespace_name = 'USERS')

以上方法需要将      d.increment_by,  d.maxblocks 这两个字段，自动扩展增量与最大文件大小，

需要将8K数据块换算成存储字节大小。文件最大32GB，就是4194302x8 。

select a.file_name,
       round(a.bytes / 1024 / 1024, 0) as TOTAL_MB,
       round(nvl(b.sb,0) / 1024 / 1024, 0) as FREE_MB,
       round(100 * NVL(b.sb,0) / a.bytes, 2) as FREE_PERC
  from dba_data_files a,
       (select file_id, sum(BYTES) sb from dba_free_space group by file_id) b
 where (a.file_id = b.file_id(+) and a.file_name like '%%%')
 order by a.file_name;

也可以是下面语句。

select c.tablespace_name "表空间"，
round(a.bytes/1024/1024,2) "表空间大小",
round((a.bytes-b.bytes)/1048576,2) "已用空间",
round(b.bytes/1048576,2) "剩余空间",
round(b.bytes/a.bytes * 100,2) "剩余百分比"
from
(select t.tablespace_name,SUM(t.bytes) bytes from dba_data_files t group by t.tablespace_name) a,
(select a.tablespace_name,NVL(SUM(b.bytes),0) bytes from dba_data_files a, dba_free_space b where a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+)
group by a.tablespace_name) b,
dba_tablespaces c
where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name
order by round(b.bytes/1024/1024,2);

使用此方法完整解决,下面的所有问题。

此方法查看数据文件大小，会出现一个问题。

空间使用光的，会显示空值。

select a.file_name,

       round(a.bytes / 1024 / 1024, 2) as TOTAL_MB,
       round(b.sb / 1024 / 1024, 2) as FREE_MB,
       round(100 * b.sb / a.bytes, 2) as FREE_PERC
  from dba_data_files a,
       (select file_id, sum(BYTES) sb from dba_free_space group by file_id) b

 where (a.file_id = b.file_id(+) and a.file_name like '%%%')

   order by a.file_name

下面这种方法，会出现一些数据文件查不出来.

      select a.file_name,round(a.bytes/1024/1024/1024,2) as TOTAL_GB,round(b.sb/1024/1024/1024,2) as FREE_GB,round(100*b.sb/a.bytes,2) as FREE_PERC
      from dba_data_files a,(select file_id,sum(BYTES) sb from dba_free_space group by file_id) b
      where a.file_id=b.file_id order by a.file_name;

下面的语句是查看表空间使用信息的，toad  for oracle 工具所使用的sql数句。

select  a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes/1048576) Max,
      c.status, c.contents
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b,
      dba_tablespaces c
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = c.tablespace_name
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,
      c.status, c.contents
from   sys.v_$TEMP_SPACE_HEADER h,
       sys.v_$Temp_extent_pool p,
       dba_temp_files f,
      dba_tablespaces c
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
and f.tablespace_name = c.tablespace_name
group by h.tablespace_name, c.status, c.contents
ORDER BY 1

下面是oracle11gr2 OEM上面查看数据文件方法：

SELECT /*+ all_rows use_concat */
 ddf.file_name,
 ddf.tablespace_name,
 ddf.online_status,
 TO_CHAR(NVL(ddf.bytes / 1024 / 1024, 0), '99999990.000'),
 TO_CHAR(DECODE(NVL(u.bytes / 1024 / 1024, 0),
                0,
                NVL((ddf.bytes - NVL(s.bytes, 0)) / 1024 / 1024, 0),
                NVL(u.bytes / 1024 / 1024, 0)),
         '99999999.999'),
 TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100),
                0,
                NVL((ddf.bytes - NVL(s.bytes, 0)) / ddf.bytes * 100, 0),
                (NVL(u.bytes, 0) / ddf.bytes * 100)),
         '990.00'),
 ddf.autoextensible
  FROM sys.dba_data_files ddf,
       (SELECT file_id, SUM(bytes) bytes
          FROM sys.dba_free_space
         GROUP BY file_id) s,
       (SELECT file_id, SUM(bytes) bytes
          FROM sys.dba_undo_extents
         WHERE status <> 'EXPIRED'
         GROUP BY file_id) u
 WHERE (ddf.file_id = s.file_id(+) and ddf.file_id = u.file_id(+) and
       ddf.file_name like '%%%')

以下方法是Oracle11gR2 上面OEM查询表空间的方法：

WITH df AS
 (SELECT tablespace_name,
         SUM(bytes) bytes,
         COUNT(*) cnt,
         DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
    FROM dba_data_files
   GROUP BY tablespace_name),
tf AS
 (SELECT tablespace_name,
         SUM(bytes) bytes,
         COUNT(*) cnt,
         DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
    FROM dba_temp_files
   GROUP BY tablespace_name)
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,
       NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
       a.autoext,
       NVL(f.bytes, 0) / 1024 / 1024,
       d.status,
       a.cnt,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM dba_tablespaces d,
       df 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.contents = 'UNDO'
   AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
   AND d.tablespace_name LIKE '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(t.ub * d.block_size, 0) / 1024 / 1024,
       NVL((t.ub * d.block_size) / a.bytes * 100, 0),
       a.autoext,
       (NVL(a.bytes, 0) / 1024 / 1024 -
       NVL((t.ub * d.block_size), 0) / 1024 / 1024),
       d.status,
       a.cnt,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM dba_tablespaces d,
       tf a,
       (SELECT ss.tablespace_name, sum(ss.used_blocks) ub
          FROM gv$sort_segment ss
         GROUP BY ss.tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+) AND
 d.tablespace_name = t.tablespace_name(+) AND
 d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' and
 d.tablespace_name LIKE '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(u.bytes, 0) / 1024 / 1024,
       NVL(u.bytes / a.bytes * 100, 0),
       a.autoext,
       NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,
       d.status,
       a.cnt,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM dba_tablespaces d,
       df a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_undo_extents
         where status in ('ACTIVE', 'UNEXPIRED')
         GROUP BY tablespace_name) u
 WHERE d.tablespace_name = a.tablespace_name(+) AND
 d.tablespace_name = u.tablespace_name(+) AND d.contents = 'UNDO' AND
 d.tablespace_name LIKE '%%'
 ORDER BY 1;