[Oracle工程师手记] 如何查看某一表空间所包含的各个对象，它们所使用空间占比？

例如，Oracle 数据库中，发生了 SYSAUX 表空间过大的情况，如何查找到哪些对象占据了较多的空间呢。可以使用如下的语句：

COL SEGMENT_NAME FORMAT A30
COL OWNER FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL SEGMENT_TYPE FORMAT A15
SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE
 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=20;  

 
下面是一个结果的例子：

SEGMENT_NAME                   OWNER      TABLESPACE   SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS        SYSAUX            200 INDEX
I_WRI$_OPTSTAT_H_ST            SYS        SYSAUX            104 INDEX
SYS_LOB0000007350C00005$$      SYS        SYSAUX        88.1875 LOBSEGMENT
SYS_LOB0000067470C00006$$      MDSYS      SYSAUX        50.1875 LOBSEGMENT
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  SYS        SYSAUX             31 INDEX
WRH$_SYSMETRIC_HISTORY_INDEX   SYS        SYSAUX             23 INDEX
WRH$_SYSMETRIC_HISTORY         SYS        SYSAUX             19 TABLE PARTITION
SYS_LOB0000069706C00004$$      SYS        SYSAUX        17.1875 LOBSEGMENT
I_WRI$_OPTSTAT_HH_ST           SYS        SYSAUX             15 INDEX
SCHEDULER$_EVENT_LOG           SYS        SYSAUX             10 TABLE
WRI$_OPTSTAT_HISTGRM_HISTORY   SYS        SYSAUX             10 TABLE PARTITION
WRI$_OPTSTAT_OPR_TASKS         SYS        SYSAUX              9 TABLE
SDO_CS_SRS                     MDSYS      SYSAUX              9 TABLE
WRI$_OPTSTAT_HISTGRM_HISTORY   SYS        SYSAUX              9 TABLE PARTITION
SYS_LOB0000070658C00002$$      MDSYS      SYSAUX         8.1875 LOBSEGMENT
SYS_LOB0000007350C00004$$      SYS        SYSAUX         7.1875 LOBSEGMENT
SYS_LOB0000069691C00004$$      SYS        SYSAUX         7.1875 LOBSEGMENT
SYS_LOB0000010641C00038$$      SYS        SYSAUX         7.1875 LOBSEGMENT
WRH$_SQL_PLAN                  SYS        SYSAUX              5 TABLE
I_WRI$_OPTSTAT_OPR_TASKS_TGST  SYS        SYSAUX              5 INDEX