SELECT
D.TABLESPACE_NAME,
SPACE
||
'M'
"SUM_SPACE(M)"
,
SPACE
- NVL (FREE_SPACE, 0) ||
'M'
"USED_SPACE(M)"
,
ROUND ( (1 - NVL (FREE_SPACE, 0) /
SPACE
) * 100, 2) ||
'%'
"USED_RATE(%)"
,
FREE_SPACE ||
'M'
"FREE_SPACE(M)"
FROM
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2)
SPACE
,
SUM
(BLOCKS) BLOCKS
FROM
DBA_DATA_FILES
GROUP
BY
TABLESPACE_NAME) D,
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM
DBA_FREE_SPACE
GROUP
BY
TABLESPACE_NAME) F
WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION
ALL
SELECT
D.TABLESPACE_NAME,
SPACE
||
'M'
"SUM_SPACE(M)"
,
USED_SPACE ||
'M'
"USED_SPACE(M)"
,
ROUND (NVL (USED_SPACE, 0) /
SPACE
* 100, 2) ||
'%'
"USED_RATE(%)"
,
NVL (FREE_SPACE, 0) ||
'M'
"FREE_SPACE(M)"
FROM
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES) / (1024 * 1024), 2)
SPACE
,
SUM
(BLOCKS) BLOCKS
FROM
DBA_TEMP_FILES
GROUP
BY
TABLESPACE_NAME) D,
(
SELECT
TABLESPACE_NAME,
ROUND (
SUM
(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (
SUM
(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM
V$TEMP_SPACE_HEADER
GROUP
BY
TABLESPACE_NAME) F
WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER
BY
1;