怎么知道一个表的各个分区在磁盘上到底占用多大空间呢？

Question

rt

Answer 1

在sqlplus中：SQL> BREAK ON SEGMENT_NAME    
SQL> COL SEGMENT_NAME FOR A30
SQL> select segment_name,partition_name,sum(bytes)/(1024*1024) "Size(M)"
  2  from dba_extents
  3  where owner='TVINFO'
  4  group by segment_name,partition_name
  5  ;
SEGMENT_NAME                   PARTITION_NAME                    Size(M)
------------------------------ ------------------------------ ----------
RATEINFO                       Y05M07                               1190
                               Y05M08                               1190
RATEINFO_STIME                 Y05M07                            64.0625
                               Y05M08                          8.0078125
SETID_STATE                    Y05M07                          972.03125
                               Y05M08                          972.03125
SID_ETIME_PID_CID              Y05M07                           96.09375
                               Y05M08                          8.0078125
SID_INDATE_PID_CID             Y05M07                           96.09375
                               Y05M08                          8.0078125
SID_STIME_PID_CID              Y05M07                           96.09375
                               Y05M08                          8.0078125
TIME_NUM                       Y05M07                                355
                               Y05M08                                355

Answer 2

上面的查询漏了一个条件SEGMENT_TYPE='TABLE PARTITION'主要是查dba_extents

Answer 3

根据segment_type分别都能得到。
SQL> select segment_type from dba_extents group by segment_type;SEGMENT_TYPE
------------------
CACHE
CLUSTER
INDEX
INDEX PARTITION
LOBINDEX
LOBSEGMENT
ROLLBACK
TABLE
TABLE PARTITION
TEMPORARY