关于数据库对象空间实际使用率

Question

DBA_SEGMENTS的BYTES是数据库对象的物理存储的空间大小，DBA_TABLES 和DBA_TAB_PARTITIONS 采用B.NUM_ROWS * B.AVG_ROW_LEN作为实际使用的空间大小，那索引的实际占用空间大小该怎么写呢，DBA_INDEXS并没有看到该怎么计算
表对象空间使用率，不知道写的对不对
SELECT B.OWNER AS TABLE_OWNER,
                     B.TABLE_NAME,
                     A.SEGMENT_TYPE,
                     B.TABLESPACE_NAME,
                     SUM(B.NUM_ROWS * B.AVG_ROW_LEN) / 1024 / 1024 TABLE_USED,
                     SUM(A.BYTES) / 1024 / 1024 TABLE_FULL,
                     TO_CHAR(ROUND((SUM(A.BYTES) - SUM(B.NUM_ROWS * B.AVG_ROW_LEN)) / SUM(A.BYTES) * 100,
                                   2),
                             '990.99') RATIO_FREE,
                     (SUM(A.BYTES) - SUM(B.NUM_ROWS * B.AVG_ROW_LEN)) / 1024 / 1024 TABLE_FREE
                FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B
               WHERE A.OWNER = B.OWNER
                 AND A.SEGMENT_NAME = B.TABLE_NAME
                 AND A.SEGMENT_TYPE = 'TABLE'
                 --AND A.OWNER IN (SELECT OWNER FROM PUB_SYS.T_USER)
                 AND A.SEGMENT_NAME NOT LIKE 'BIN%'
               GROUP BY B.OWNER, B.TABLE_NAME, B.TABLESPACE_NAME, A.SEGMENT_TYPE
              UNION ALL
              SELECT B.TABLE_OWNER,
                     B.TABLE_NAME,
                     A.SEGMENT_TYPE,
                     B.TABLESPACE_NAME,
                     SUM(B.NUM_ROWS * B.AVG_ROW_LEN) / 1024 / 1024 TABLE_USED,
                     SUM(A.BYTES) / 1024 / 1024 TABLE_FULL,
                     TO_CHAR(ROUND((SUM(A.BYTES) - SUM(B.NUM_ROWS * B.AVG_ROW_LEN)) / SUM(A.BYTES) * 100,
                                   2),
                             '990.99') RATIO_FREE,
                     (SUM(A.BYTES) - SUM(B.NUM_ROWS * B.AVG_ROW_LEN)) / 1024 / 1024 TABLE_FREE
                FROM SYS.DBA_SEGMENTS A, SYS.DBA_TAB_PARTITIONS B
               WHERE A.OWNER = B.TABLE_OWNER
                 AND A.SEGMENT_NAME = B.TABLE_NAME
                 AND A.PARTITION_NAME = B.PARTITION_NAME
                 AND A.SEGMENT_TYPE = 'TABLE PARTITION'
                 --AND A.OWNER IN (SELECT OWNER FROM PUB_SYS.T_USER)
                 AND A.SEGMENT_NAME NOT LIKE 'BIN%'
               GROUP BY B.TABLE_OWNER, B.TABLE_NAME, B.TABLESPACE_NAME, A.SEGMENT_TYPE;
没分了~~~

Answer 1

index的也在user/all/dba_segements中，它和table都是在段里存储数据。