close
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
文章標籤
全站熱搜