Oracle Tablo Boyutu Sorgulamak

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;

Tagged: , , , , , ,

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir