兩隻小虎,一隻豬

Oracle 計算每一個Table 所佔的空間可以用DBA_TABLES這個View 來查

SQL語法

SELECT A.TABLE_NAME , A.TABLE_SIZE_GB , B.DATA_SIZE_GB
FROM (   
        SELECT SEGMENT_NAME AS TABLE_NAME, 
            ROUND(SUM(BYTES) /(1024*1024*1024),2) AS TABLE_SIZE_GB 
        FROM USER_EXTENTS 
        WHERE SEGMENT_NAME ='TABLE_NAME'
        GROUP BY SEGMENT_NAME 
    ) A LEFT JOIN (
        SELECT TABLE_NAME , ROUND (AVG_ROW_LEN * NUM_ROWS /(1024*1024*1024),2) AS DATA_SIZE_GB  
        FROM DBA_TABLES 
        WHERE TABLE_NAME = 'TABLE_NAME'
    ) B ON A.TABLE_NAME = B.TABLE_NAME

計算Tablespace Size 需要用到DBA_DATA_FILES 和DBA_FREE_SPACE這兩個View

SELECT A.TABLESPACE_NAME , 
  ROUND(B.FREE_GB,2) AS FREE_GB , 
  ROUND(A.TOTAL_GB - b.FREE_GB) AS USED_GB ,
  ROUND(A.TOTAL_GB,2) AS TOTAL_GB , 
  ROUND(((A.TOTAL_GB - B.FREE_GB)/ A.TOTAL_GB )*100,2) AS USED_PERCENT,
  ROUND((B.FREE_GB/ A.TOTAL_GB )*100,2) AS FREE_PERCENT
FROM     
  (
    SELECT TABLESPACE_NAME, 
      SUM(BYTES)/ (1024*1024*1024) AS TOTAL_GB
    FROM DBA_DATA_FILES  
    WHERE TABLESPACE_NAME='TABLESPACE_NAME'
    GROUP  BY TABLESPACE_NAME
  )A,
  (
    SELECT TABLESPACE_NAME,    
      SUM(BYTES) / (1024*1024*1024) AS FREE_GB 
    FROM DBA_FREE_SPACE 
    WHERE  TABLESPACE_NAME='TABLESPACE_NAME'
    GROUP BY TABLESPACE_NAME
   ) B
WHERE A.TABLESPACE_NAME= B.TABLESPACE_NAME