Etiket arşivi: database tablo boyutu sorgulamak

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;