select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
round(maxbytes/1048576,2) Max
from (
select f.tablespace_name, sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name
) a,(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name (+)
union
select tablespace_name,
round(sum(bytes_used + bytes_free) / 1048576, 2),
round(sum(bytes_free) / 1048576,2),
round(sum(bytes_used) / 1048576,2),
round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_Free,
100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_used,
round(max(bytes_used + bytes_free) / 1048576, 2)
from sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
round(maxbytes/1048576,2) Max
from (
select f.tablespace_name, sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name
) a,(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name (+)
union
select tablespace_name,
round(sum(bytes_used + bytes_free) / 1048576, 2),
round(sum(bytes_free) / 1048576,2),
round(sum(bytes_used) / 1048576,2),
round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_Free,
100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2) Pct_used,
round(max(bytes_used + bytes_free) / 1048576, 2)
from sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1
也可以几个用户共用表空间
select * from dba_users
group by tablespace_name;
group by tablespace_name;ERROR at line 1:
ORA-00942: table or view does not exist没有dba_free_space这个表啊
或者你以system用户登陆查看吧
SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "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 ,
MAX(BYTES/1024/1024) MAX,
MIN(BYTES/1024/1024) MIN
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "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(+)