表空間使用量查詢

wpgy發表於2020-03-19
SET LINE 200
COL TABLESPACE_NAME FOR A30

WITH
F AS (SELECT TABLESPACE_NAME ,SUM(BYTES) FREE_SUM_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME),
D AS (SELECT TABLESPACE_NAME ,SUM(BYTES) TOTAL_SUM_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)
SELECT
D.TABLESPACE_NAME
,ROUND(D.TOTAL_SUM_BYTES/1024/1024/1024, 2) TOTAL_GB
,ROUND((D.TOTAL_SUM_BYTES - F.FREE_SUM_BYTES)/1024/1024/1024, 2) USED_GB
,NVL(ROUND(F.FREE_SUM_BYTES/1024/1024/1024, 2), 0) FREE_GB
,NVL(ROUND((D.TOTAL_SUM_BYTES - F.FREE_SUM_BYTES)*100/D.TOTAL_SUM_BYTES, 2), 100) USED_PERCENT
,NVL(ROUND(F.FREE_SUM_BYTES*100/D.TOTAL_SUM_BYTES, 2), 0) FREE_PERCENT
FROM D, F
WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
ORDER BY FREE_PERCENT;

WITH
C AS (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME),
D AS (SELECT TABLESPACE_NAME,SUM(BYTES_CACHED) BYTES_USED FROM V$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME)
SELECT
C.TABLESPACE_NAME
, ROUND(C.BYTES/1024/1024/1024, 2) TOTAL_SIZE_GB
, ROUND((C.BYTES-D.BYTES_USED)/1024/1024/1024, 2) FREE_SIZE_GB
, ROUND(D.BYTES_USED/1024/1024/1024, 2) USED_SIZE_GB
, ROUND(D.BYTES_USED*100/C.BYTES, 2) USED_PCT
FROM C, D
WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME
;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31560527/viewspace-2681493/，如需轉載，請註明出處，否則將追究法律責任。

相關文章