重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 如何查看ORACLE空间占用排名前10的对象，包含大对象 [打印本页]

作者: 郑全 时间: 4 天前
标题: 如何查看ORACLE空间占用排名前10的对象，包含大对象
select  a.owner,a.segment_type,a.segment_name,b.table_name "大对象对应表名",b.column_name as "大对象对应表字段",
   b.segment_name,a.tablespace_name,round(a.bytes/1024/1024/1024) as "Size(GB)"
from dba_segments a,dba_lobs b
where a.segment_name=b.segment_name(+)
  and a.owner=b.owner (+)
order by 8 desc
FETCH FIRST 10 ROWS ONLY;

   OWNER SEGMENT_TYPE SEGMENT_NAME 大对象对应表名大对象对应表字段 SEGMENT_NAME TABLESPACE_NAME Size(GB)
1 HIS50 TABLE          HEALTHMONITORING_PHA_STOCKINFO HIS_DATA 495
2 CAP LOBSEGMENT SYS_LOB0000230028C00003$$ CAP_CA_LOG RESP_PARAM SYS_LOB0000230028C00003$$ CAP_LOG 187
3 HIS50 LOBSEGMENT SYS_LOB0000137366C00020$$ T_SI_CALLINTERFACE_RECORDINFO INPUT SYS_LOB0000137366C00020$$ HIS 105
4 HIT_MDC LOB PARTITION SYS_LOB0000132353C00002$$ MDC_RCD_IN_RECORD_DATA DATA SYS_LOB0000132353C00002$$ MDC2025 99
5 HIT_MDC LOB PARTITION SYS_LOB0000131910C00002$$ MDC_RCD_OUT_RECORD_DATA DATA SYS_LOB0000131910C00002$$ MDC2025 93
6 HIS50 TABLE T_SI_CALLINTERFACE_RECORDINFO HIS 81
7 HIT_MDC LOB PARTITION SYS_LOB0000132353C00002$$ MDC_RCD_IN_RECORD_DATA DATA SYS_LOB0000132353C00002$$ MDC2026 60
8 PACS55 LOBSEGMENT SYS_LOB0000137457C00003$$ PACS_RO_NOTIFY_LOG SENDXML SYS_LOB0000137457C00003$$ PACS55 57
9 HIS50 TABLE PHA_TRACE_UPLOAD_LOG HIS 56
10 HIT_MDC LOB PARTITION SYS_LOB0000131910C00002$$ MDC_RCD_OUT_RECORD_DATA DATA SYS_LOB0000131910C00002$$ MDC2026 45

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)