Oracle 计算.索引数据大小,表数据大小,以及表分区中某个分区的数据大小

--计算全局非分区索引占用空间大小

 SELECT SEGMENT_NAME AS INDEX_NAME,
         SUM(BYTES) / (1024 * 1024 * 1024) "INDEX_SIZE[GB]"
    FROM DBA_SEGMENTS
   WHERE SEGMENT_TYPE = 'INDEX'
     AND SEGMENT_NAME = 'PDCA_SN_DETAIL_T_N01'
   GROUP BY SEGMENT_NAME;

--查找全局索引对应的表空间，对应表，占用表空间大小

SELECT T1.INDEX_NAME,T1.Table_Name, T1.TABLESPACE_NAME, T2.BYTES / (1024 * 1024 * 1024) G
  FROM DBA_INDEXES T1, DBA_SEGMENTS T2
 WHERE T1.INDEX_NAME = T2.SEGMENT_NAME
   AND T2.SEGMENT_TYPE = 'INDEX'
   AND T1.INDEX_NAME IN
       ('PDCA_LOGINFO_IDX_N1', 'BUSTB_ATTRIBUTE_IDX_N1',
        'PDCA_ERROR_LOG_INX2', 'PDCA_ERROR_LOG_INX1',
        'PDCA_SN_WIP_KEYPARTS_T_IN1', 'PDCA_SN_DETAIL_T_N03',
        'PDCA_SN_DETAIL_T_N02', 'PDCA_SN_DETAIL_T_N01',
        'MOBILE_CODE_RELATION_IDX_N4', 'MOBILE_CODE_IDX_N1',
        'MOBILE_CODE_RELATION_IDX_N2', 'PDCA_SN_WIP_KEYPARTS_T_IN3',
        'MOBILE_CODE_RELATION_IDX_N3', 'PDCA_SN_WIP_KEYPARTS_T_IN2',
        'BUSTB_TESTSUMMARY_INDX3', 'MOBILE_CODE_RELATION_IDX_N5',
        'BUSTB_MAC_CODE_T_HIS_U1', 'PDCA_RTY_TARGET_T_INDX3',
        'PDCA_SN_WIP_TRACKING_T_N05', 'PDCA_SN_WIP_TRACKING_T_U01',
        'BUSTB_MOBILE_INBOUND_INFO_IDX2', 'BUSTB_MOBILE_SN_N2')
 ORDER BY G DESC;

--计算所有索引子分区占用空间大小

SELECT SEGMENT_NAME AS INDEX_NAME,
         SUM(BYTES) / (1024 * 1024 * 1024) G
    FROM DBA_SEGMENTS
   WHERE SEGMENT_TYPE = 'INDEX SUBPARTITION'
   GROUP BY SEGMENT_NAME
   ORDER BY G DESC

--计算子分区索引RESULT_PART_201610_62551所占空间

SELECT PARTITION_NAME, SUM(BYTES) / (1024 * 1024 * 1024) G
     FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'INDEX SUBPARTITION'
      AND SEGMENT_NAME = 'BUSTB_TESTRESULT_IDX1'
      AND partition_name = 'RESULT_PART_201610_62551'
    GROUP BY PARTITION_NAME


--计算2016年10月每个子分区索引所占空间大小

  SELECT PARTITION_NAME, SUM(BYTES) / (1024 * 1024 * 1024) G
     FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'INDEX SUBPARTITION'
      AND SEGMENT_NAME = 'BUSTB_TESTRESULT_IDX1'
      AND partition_name LIKE  'RESULT_PART_201610%'
    GROUP BY PARTITION_NAME
    
    
 --计算 2016年10月 分区索引所占空间大小
     SELECT SEGMENT_NAME AS INDEX_NAME, SUM(BYTES) / (1024 * 1024 * 1024) G
     FROM DBA_SEGMENTS
    WHERE SEGMENT_TYPE = 'INDEX SUBPARTITION'
      AND SEGMENT_NAME = 'BUSTB_TESTRESULT_IDX1'
      AND partition_name LIKE  'RESULT_PART_201610%'
    GROUP BY SEGMENT_NAME

-- 计算BUSTB_MOBILE_SN数据大小

SELECT SEGMENT_NAME TABLE_NAME,
       SUM(BLOCKS) BLOCKS,
       SUM(BYTES) / (1024 * 1024 * 1204) "TABLE_SIZE[GB]"
  FROM USER_SEGMENTS
 WHERE SEGMENT_NAME = 'BUSTB_MOBILE_SN'
   AND SEGMENT_TYPE = 'TABLE'
 GROUP BY SEGMENT_NAME;


-- 计算某个没有子分区的分区大小
 SELECT SEGMENT_NAME AS TABLE_NAME,
        PARTITION_NAME,
        BYTES / (1024 * 1024 * 1204) "TABLE_SIZE[GB]"
   FROM DBA_SEGMENTS T
  WHERE T.SEGMENT_NAME = 'T_PART'
    AND SEGMENT_TYPE = 'TABLE PARTITION'
    AND PARTITION_NAME = 'P1';

-- 对子分区求和，计算分区总大小

 SELECT SEGMENT_NAME AS TABLE_NAME,
        SUM(BYTES) / (1024 * 1024 * 1024) "TABLE_SIZE[GB]"
   FROM DBA_SEGMENTS T
  WHERE T.SEGMENT_NAME = 'BUSTB_TESTRESULT'
    AND SEGMENT_TYPE = 'TABLE SUBPARTITION'
    AND PARTITION_NAME LIKE 'RESULT_PART_201301%'
  GROUP BY SEGMENT_NAME