set linesize 400;
setpagesize 200;
col"USED_RATE(%)"format a20;
col"USED_SPACE(M)"format a20;
col"SUM_SPACE(M)"format a20;
col"FREE_SPACE(M)"format a20;
SELECT
D.TABLESPACE_NAME,
SPACE 'M'AS"SUM_SPACE(M)",
BLOCKS AS"SUM_BLOCKS",
SPACE -NVL(FREE_SPACE, 0) 'M'AS"USED_SPACE(M)",
ROUND((1 -NVL(FREE_SPACE, 0)/ SPACE)* 100, 2) '%'AS"USED_RATE(%)",
FREE_SPACE 'M'AS"FREE_SPACE(M)"
FROM(
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024 * 1024), 2)AS SPACE,SUM(BLOCKS)AS BLOCKS
FROM DBA_DATA_FILES
GROUPBY TABLESPACE_NAME
) D
INNER JOIN (
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024 * 1024), 2)AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUPBY TABLESPACE_NAME
) F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME
UNIONALL
SELECT
D.TABLESPACE_NAME,
SPACE 'M'AS"SUM_SPACE(M)",
BLOCKS AS SUM_BLOCKS,
USED_SPACE 'M'AS"USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0)/ SPACE * 100, 2) '%'AS"USED_RATE(%)",
NVL(FREE_SPACE, 0) 'M'AS"FREE_SPACE(M)"
FROM(
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024 * 1024), 2)AS SPACE,SUM(BLOCKS)AS BLOCKS
FROM DBA_TEMP_FILES
GROUPBY TABLESPACE_NAME
) D
INNER JOIN (
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024 * 1024), 2)AS USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024 * 1024), 2)AS FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUPBY TABLESPACE_NAME
) F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDERBY 1;
--转自