--Oracle中对表上的lob字段是单独存储的,dba_segments表占用的空间+lob segments占的空间 + lob索引SELECT SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024,2)||'M'FROM(SELECT S.SEGMENT_NAME, S.BYTES FROM DBA_SEGMENTS S
WHERE S.OWNER ='USER1'AND S.SEGMENT_NAME IN('TABLETEST1')UNIONALLSELECT L.TABLE_NAME, S.BYTES FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE L.SEGMENT_NAME = S.SEGMENT_NAME AND S.OWNER ='USER1'AND L.OWNER ='USER1'AND L.TABLE_NAME IN('TABLETEST1')UNIONALLSELECT I.TABLE_NAME, S.BYTES FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE I.INDEX_NAME = S.SEGMENT_NAME AND I.INDEX_TYPE ='LOB'AND S.OWNER ='USER1'AND I.OWNER ='USER1'AND I.TABLE_NAME IN('TABLETEST1'))GROUPBY SEGMENT_NAME
--查询表+索引+blob+分区大小SELECT OWNER, TABLE_NAME, TRUNC(SUM(BYTES)/1024/1024)"MEG(M)",ROUND( RATIO_TO_REPORT(SUM(BYTES))OVER()*100)PERCENTFROM(SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN('TABLE','TABLE PARTITION','TABLE SUBPARTITION')UNIONALLSELECT I.TABLE_NAME, I.OWNER, S.BYTES
FROM DBA_INDEXES I, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = I.INDEX_NAME
AND S.OWNER = I.OWNER
AND S.SEGMENT_TYPE IN('INDEX','INDEX PARTITION','INDEX SUBPARTITION')UNIONALLSELECT L.TABLE_NAME, L.OWNER, S.BYTES
FROM DBA_LOBS L, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE IN('LOBSEGMENT','LOB PARTITION')UNIONALLSELECT L.TABLE_NAME, L.OWNER, S.BYTES
FROM DBA_LOBS L, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = L.INDEX_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE ='LOBINDEX')WHERE OWNER IN UPPER('USER1')GROUPBY TABLE_NAME, OWNER
--HAVING SUM(BYTES)/1024/1024 > 10 /* IGNORE REALLY SMALL TABLES */ORDERBYSUM(BYTES)DESC--查询表空间表SELECTdistinct OWNER, TABLE_NAME FROM dba_tables where TABLESPACE_NAME ='TBS_USER'unionallSELECTdistinct TABLE_OWNER, TABLE_NAME FROM dba_tab_partitions where TABLESPACE_NAME ='TBS_USER';