【Oracle SQL】表大小

---

查询表实际存储大小

--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')
UNION ALL
SELECT 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')
UNION ALL
SELECT 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')
)
GROUP BY SEGMENT_NAME


--查询表+索引+blob+分区大小
SELECT OWNER, TABLE_NAME, TRUNC(SUM(BYTES)/1024/1024) "MEG(M)", ROUND( RATIO_TO_REPORT( SUM(BYTES) ) OVER () * 100) PERCENT
FROM (
SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT 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')
UNION ALL
SELECT 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')
UNION ALL
SELECT 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')
GROUP BY TABLE_NAME, OWNER
--HAVING SUM(BYTES)/1024/1024 > 10  /* IGNORE REALLY SMALL TABLES */
ORDER BY SUM(BYTES) DESC

--查询表空间表
SELECT distinct OWNER, TABLE_NAME FROM dba_tables where TABLESPACE_NAME = 'TBS_USER'
union all
SELECT distinct TABLE_OWNER, TABLE_NAME FROM dba_tab_partitions where TABLESPACE_NAME = 'TBS_USER'
;