SELECT
A.GROUP_ID
,B.NAME
,A.TOTAL_M
,A.FREE_M
,A.TOTAL_M - A.FREE_M USED_M
,A.MAX_M
,CAST((A.TOTAL_M - A.FREE_M)/ A.TOTAL_M AS DEC(5,4)) RATE_USED
,CAST(A.TOTAL_M / A.MAX_M AS DEC(5,4)) MAXRATE_USED
FROM
(
SELECT
GROUP_ID ,
SUM(TOTAL_SIZE)*AVG(PAGE_SIZE)/1024/1024 TOTAL_M,
SUM(FREE_SIZE) *AVG(PAGE_SIZE)/1024/1024 FREE_M ,
SUM(MAX_SIZE) MAX_M
FROM
V$DATAFILE
GROUP BY
GROUP_ID
)
A,
V$TABLESPACE B
WHERE
A.GROUP_ID = B.ID
ORDER BY
GROUP_ID;
SELECT
A.GROUP_ID
,B.NAME
,A.TOTAL_M
,A.FREE_M
,A.TOTAL_M - A.FREE_M USED_M
,A.MAX_M
,CAST((A.TOTAL_M - A.FREE_M)/ A.TOTAL_M AS DEC(5,4)) RATE_USED
,CAST(A.TOTAL_M / A.MAX_M AS DEC(5,4)) MAXRATE_USED
FROM
(
SELECT
GROUP_ID ,
SUM(TOTAL_SIZE)*AVG(PAGE_SIZE)/1024/1024 TOTAL_M,
SUM(FREE_SIZE) *AVG(PAGE_SIZE)/1024/1024 FREE_M ,
SUM(MAX_SIZE) MAX_M
FROM
V$DATAFILE
GROUP BY
GROUP_ID
)
A,
V$TABLESPACE B
WHERE
A.GROUP_ID = B.ID
ORDER BY
GROUP_ID;