oracle查看object对象使用空间

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:oracle查看object对象使用空间

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

1、表空间大小

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by Sum(bytes)/1024/1024 desc;

2、表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE'  group by segment_name order by segment_name;
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc;

3、索引占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='INDEX'  group by segment_name order by segment_name;

4、数据文件使用情况

select a.tablespace_name,
       round(a.bytes / 1024 / 1024, 0)"总空间",
       round((decode(b.bytes, null, 0, b.bytes)) / 1024 / 1024, 0)"使用空间",
       round((decode(b.bytes, null, 0, b.bytes)) / a.bytes * 100, 1)"使用率",
       c.file_name,
       c.status
  from sys.sm$ts_avail a, sys.sm$ts_free b, dba_data_files c
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name
 order by a.tablespace_name;

-----------------------------------------------------------------------
select b.file_id 文件ID,
       b.tablespace_name 表空间,
       b.file_name 物理文件名,
       b.bytes / 1024 / 1024 大小M,
       c.max_extents / 1024 / 1024 可扩展数M,
       b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024 总大小M,
       trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024)  已使用M,
       trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024)  剩余M,
       trunc(sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2)  剩余比
  from dba_free_space a, dba_data_files b, dba_tablespaces c
 where a.file_id = b.file_id
   and b.tablespace_name = c.tablespace_name
 group by b.tablespace_name,
          b.file_name,
          b.file_id,
          b.bytes,
          c.max_extents,
          b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024
 order by b.file_id;

5、表空间使用统计

select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

oracle查看object对象使用空间》有 1 条评论

  1. 惜分飞 说:

    表空间大小估算

    select a.tablespace_name,
           round(a.s,2) "CURRENT_TOTAL(MB)",
           round((a.s - f.s),2) "USED(MB)",
           f.s "FREE(MB)",
           round(f.s / a.s * 100, 2) "FREE%",
           g.autoextensible,
           round(a.ms,2) "MAX_TOTAL(MB)"
      from (select d.tablespace_name,
                   sum(bytes / 1024 / 1024) s,
                   sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
              from dba_data_files d
             group by d.tablespace_name) a,
           (select f.tablespace_name, sum(f.bytes / 1024 / 1024) s
              from dba_free_space f
             group by f.tablespace_name) f,
           (select distinct tablespace_name, autoextensible
              from DBA_DATA_FILES
             where autoextensible = 'YES'
            union
            select distinct tablespace_name, autoextensible
              from DBA_DATA_FILES
             where autoextensible = 'NO'
               and tablespace_name not in
                   (select distinct tablespace_name
                      from DBA_DATA_FILES
                     where autoextensible = 'YES')) g
     where a.tablespace_name = f.tablespace_name
       and g.tablespace_name = f.tablespace_name order by "FREE%";
    

    准确计算表空间大小

    SELECT F.TABLESPACE_NAME,
           A.ALL_TOTAL "总空间",
           A.ALL_USED "总使用空间",
           A.ALL_TOTAL - A.ALL_USED "总剩余空间",
           (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 "总剩余比例",
           A.TOTAL "当前大小",
           U.USED "当前使用空间",
           F.FREE "当前剩余空间",
           (U.USED / A.TOTAL) * 100"当前使用比例",
           (F.FREE / A.TOTAL) * 100 "当前剩余比例"
      FROM (SELECT TABLESPACE_NAME,
                   SUM(BYTES / (1024 * 1024 * 1024)) TOTAL,
                   SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES) /
                       (1024 * 1024 * 1024)) ALL_TOTAL,
                   SUM(USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
              FROM DBA_DATA_FILES
             GROUP BY TABLESPACE_NAME) A,
           (SELECT TABLESPACE_NAME, SUM(BYTES / (1024 * 1024 * 1024)) USED
              FROM DBA_EXTENTS
             GROUP BY TABLESPACE_NAME) U,
           (SELECT TABLESPACE_NAME, SUM(BYTES / (1024 * 1024 * 1024)) FREE
              FROM DBA_FREE_SPACE
             GROUP BY TABLESPACE_NAME) F
     WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
       AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
     ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F.FREE / A.TOTAL ASC;