Oracle Tablo Boyutu Sorgulamak.
Aşağıdaki sql ile tablo boyutlarını öğrenebiliriz.
Tablo size’ına tablo verisi, index boyutu,lob alanlar ve lob indexler dahildir.
SELECT OWNER, TABLE_NAME, TRUNC(SUM(BYTES) / 1024 / 1024) MEG
FROM (SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
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 = 'INDEX'
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 = 'LOBSEGMENT'
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 LIKE UPPER('&owner')
GROUP BY TABLE_NAME, OWNER
-- tablo size 100 MB dan büyükler gelsin
HAVING SUM(BYTES) / 1024 / 1024 > 100
ORDER BY SUM(BYTES) DESC;