Oracle中查询带有lob字段的表的大小

来源：互联网发布：上海淘宝运营培训班编辑：程序博客网时间：2024/05/20 00:35

Oracle中 lob字段采用独立的lob segment来存储，因此表的大小不能只查看dba_segments.

mos给出了查询语句：

How to Compute the Size of a Table containing Outline CLOBs and BLOBs (文档 ID 118531.1)

ACCEPT SCHEMA PROMPT 'Table Owner: 'ACCEPT TABNAME PROMPT 'Table Name:  'SELECT (SELECT SUM(S.BYTES)                                                                                                 -- The Table Segment size  FROM DBA_SEGMENTS S  WHERE S.OWNER = UPPER('&SCHEMA') AND       (S.SEGMENT_NAME = UPPER('&TABNAME'))) + (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Segment Size  FROM DBA_SEGMENTS S, DBA_LOBS L  WHERE S.OWNER = UPPER('&SCHEMA') AND       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) + (SELECT SUM(S.BYTES)                                                                                                 -- The Lob Index size  FROM DBA_SEGMENTS S, DBA_INDEXES I  WHERE S.OWNER = UPPER('&SCHEMA') AND       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))  "TOTAL TABLE SIZE"FROM DUAL;

示例：

单独至查询dba_segments查看fga_log$表大小：SQL> select sum(bytes)/1024/1024/1024 as GB from dba_segments where  segment_name='FGA_LOG$';         GB----------45.5166016通过MOS提供的脚本来查询：SELECT (SELECT NVL(SUM(S.BYTES),0)  FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('sys') AND (S.SEGMENT_NAME = UPPER('fga_log$'))) + (SELECT NVL(SUM(S.BYTES),0)  FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('sys') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('fga_log$') AND L.OWNER = UPPER('sys'))) + (SELECT NVL(SUM(S.BYTES),0)  FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('sys') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('fga_log$') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('sys'))) "TOTAL TABLE SIZE" FROM DUAL;TOTAL TABLE SIZE----------------     87638999040

0 0