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博客 ” ，链接：https://blog.itpub.net/31560527/viewspace-2681493/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
shell执行小数运算
请登录后发表评论
登录
全部评论
